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