Pages

Nov 17, 2013

Finding the N th entry of a column in SQL

Let us take a simple table fruit and look how the query works

SQL> select * from fruit order by name asc;

NAME
----------
apple
banana
grape
orange

#If we need to find the second Maximum entry, The query would be:
#Sort the query in ascending order, then pick the second Maximum entry from the resultant set
SQL> select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name asc) where rownum <= &n );
Enter value for n: 2
old   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= &n )
new   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= 2 )

MAX(NAME)
----------
banana


#If we need to find the second Minimum entry, The query would be:
#Sort the query in the descending order, then pick the second Minimum entry from the resultant set
SQL> select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n);
Enter value for n: 2
old   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n)
new   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= 2)

MIN(NAME)
----------
grape

In order to get proper results, use the right combination of order by desc/asc and max/min feature of Query 

No comments:

Post a Comment