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 

1 comment:

  1. Earn from Ur Website or Blog thr PayOffers.in!

    Hello,

    Nice to e-meet you. A very warm greetings from PayOffers Publisher Team.

    I am Sanaya Publisher Development Manager @ PayOffers Publisher Team.

    I would like to introduce you and invite you to our platform, PayOffers.in which is one of the fastest growing Indian Publisher Network.

    If you're looking for an excellent way to convert your Website / Blog visitors into revenue-generating customers, join the PayOffers.in Publisher Network today!


    Why to join in PayOffers.in Indian Publisher Network?

    * Highest payout Indian Lead, Sale, CPA, CPS, CPI Offers.
    * Only Publisher Network pays Weekly to Publishers.
    * Weekly payments trough Direct Bank Deposit,Paypal.com & Checks.
    * Referral payouts.
    * Best chance to make extra money from your website.

    Join PayOffers.in and earn extra money from your Website / Blog

    http://www.payoffers.in/affiliate_regi.aspx

    If you have any questions in your mind please let us know and you can connect us on the mentioned email ID info@payoffers.in

    I’m looking forward to helping you generate record-breaking profits!

    Thanks for your time, hope to hear from you soon,
    The team at PayOffers.in

    ReplyDelete