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