This query has become the mostly asked question in any Oracle technical Interview. Following is an excerpt tip which comes from Ramcharan Karthic, software engineer in
Note: This tip was published in the January/February 2003 issue of Oracle Magazine.
This Select query will help you select the Nth Max or Min value from any table.
For example, consider a table TAB1 in which you want to find the Nth Max or Min from the column, COL1.
First, the query for Max:
SELECT * FROM TAB1 a
WHERE &N = (SELECT count (DISTINCT (b.col1))
FROM TAB1 b WHERE a.col1<=b.col1)
Next, the query for Min:
SELECT * FROM TAB1 a
WHERE &N = (SELECT count (DISTINCT (b.col1))
FROM TAB1 b WHERE a.col1>=b.col1)
If N=1 will return first max or first min. N=2 will return second max or min.
