Get Nth highest record from table

Hello friends,

Recently in one interview i had question for get 3rd highest amount  record from table ,

i had make query with sub-query , but interviewer had something different in mind and told me any another option, so here i will  post some of the option to get nth highest record…

1) select * from (select year,amount,row_number() over (order by amount desc) rn from budget) where rn = 3;

2) SELECT *  FROM budget A WHERE &N = (SELECT COUNT (DISTINCT (b.amount)) FROM budget B WHERE a.amount<=b.amount);

here we have to give value N = 3

3)select amount from (select amount from (select distinct amount from ( select distinct amount from budget order by amount desc) where rownum<=3) order by amount asc) where rownum<=1;

4) select amount from (select rownum r1,amount from (select distinct(amount) from budget where amount is NOT NULL order by amount desc)) where  r1 = 3;

5) SELECT LEVEL,MAX(amount) FROM budget WHERE LEVEL IN (3) CONNECT BY PRIOR amount > amount GROUP BY LEVEL;

here, interestingly we can get 2nd and 3rd most highest amount record also…

SELECT LEVEL,MAX(amount) FROM budget WHERE LEVEL IN (2,3) CONNECT BY PRIOR amount > amount GROUP BY LEVEL;

 

I had find this much , you have to try for more option and if you will get it then please post in comments.

Cheers….  🙂

Advertisements

About Sanju
I am Software Programmer. I am working in JAVA/J2EE Technologies.

One Response to Get Nth highest record from table

  1. Abha says:

    other option for finding nth highest record is: ex. for 10th highest record

    select e1.salary from emp e1
    where (9) = (select distinct count(e2.salary) from emp e2 where e2.salary > e1.salary)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: