Selecting Nth MAX or MIN

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 Bangalore, India.

 

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.