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:
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.
