SQL to Return the nth Maximum Value

Sometimes what seems like a simple request might take a little bit of thinking to devise and implement a solution. Recently, I was asked how to write SQL that returns the nth maximum value from a column. If you think about it, it isn't too difficult.

Assume that you have a table with 10 rows, and there is a column in that table that holds the values 1 through 10. The idea is to create a query that for the 7th maximum value would return a 7, the 8th an 8, and so on.

This can be accomplished with a common table expression (CTE) and the following query:

WITH TOPN AS 

   (
    SELECT YOUR_COLUMN 
    FROM    YOUR_TABLE
    ORDER BY YOUR_COLUMN DESC
    FETCH FIRST 7 ROWS ONLY

   )
SELECT MIN(YOUR_COLUMN)
FROM   TOPN;

Simply change the value of FETCH FIRST from 7 to whatever you wish n to be.

If there are duplicate values and you want to eliminate them from consideration, just change the SELECT to SELECT DISTINCT in the TOPN common table expression.

This should work for any type of values (numeric, character, date/time) that you wish to query.