Posts
33
Comments
186
Trackbacks
0
SQL Server–Find Nth Largest/smallest entry in a table

We could find the maximum and minimum value in a table by using the SQL inbuilt aggregate functions MAX() and MIN() respectively. But, many times there comes a scenario when we need to find the second or third largest/smallest entry in table. There is no built-in SQL function to get this information but we can use Row_Number() function to our advantage to find Nth Largest or smallest value in table.

For example – usually, to find maximum value we normally use the following

select MAX(column_name) as column_name from table_name 

This is equivalent to the following

SELECT * FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY column_name DESC) ROW_NUM, column_name FROM table_name
)  T 
WHERE row_num = 1
However, this can then be used to get any largest value in the table, all you have to do is just change the row_num value in the where clause.
So, to get the second largest value set 
WHERE row_num = 2

Similarly to find the second smallest entry, just sort the column by ascending and set the where clause to 2.

Hope this helps!

posted on Tuesday, March 12, 2013 3:45 PM Print
Comments
Gravatar
# re: SQL Server–Find Nth Largest/smallest entry in a table
Anonymous
2/9/2015 11:26 AM
Don't think this will work.
Rownum gets updated as and when a record is retrieved.
Gravatar
# re: SQL Server–Find Nth Largest/smallest entry in a table
Vipin
12/8/2015 12:56 PM
But, you can use the order by clause to get the desired sorting prior to pikcing up value based on RowNum

Post Comment

Title *
Name *
Email
Comment *  
Verification