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!