How can write a SELECT statement that finds the n greatest values in a column? From what i’ve read, the TOP statement only returns the first n rows, it doesn’t take any regard to the values. For example, if a have a column with ages, i want to be able to find the 5 oldest people.
3 thoughts on “T-SQL: How do I find the greatest values in a column?”
Leave a Reply
You must be logged in to post a comment.
You can use the ORDER BY directive to sort by columns then use TOP to grab what you need. Don’t forget that you can also sort ascending or descending.
if you’ve got an age column you can find the 5 oldest by sorting so that high ages come first and then grabbing just the first 5 records
select top 5 * from People
order by Age desc
or
or if you’ve got a date of birth column, you can sort that so that the earliest date comes first and grab the first 5 records from there
select top 5 * from People
order by DateOfBirth asc
This is simple
to find top 5 people who are oldest
1. Order you select clause here it will be in ascending order b y Date Of birth and the older the date the older the person.
2. select top 5 from the records set
Table Structure: Table Name t_People
People_ID
Name
Date_Of _Birth
SQL:
Select Top 5 Name
From t_People
Order by Date_Of _Birth Asc