Selecting Nth MAX or MIN record detail

This tip 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 

 WHERE a.col1>=b.col1) 

If N=1 will return first max or first min. N=2 will return second max or min.

If for example TAB1 contains fields #userid, #region_accessed, #no_of_sessions and we need to find

each unique userid with region from which he accessed most(max no_of_sessions) we can write the SQL like:

 SELECT * FROM  TAB1 a   WHERE  1 = (SELECT count(DISTINCT(b.no_of_sessions))  

 FROM TAB1 b WHERE a.userid=b.userid and a.no_of_sessions<=b.no_of_sessions)