Second, (n)th Biggest and (n)th Lowest Amount from a Table using Oracle

Oracle :

Second (2nd) Highest Amount from a Table :

Method 1 :

select loanamt from

       (select loanamt from

       (select distinct loanamt from floaa order by loanamt desc)

            where rownum <= 2 order by loanamt asc)

            where rownum = 1;

Method 2 :

select max(loanamt) from floaa

          where loanamt not in (select max(loanamt) from floaa)

Method 3 :

select max(loanamt) from floaa where loanamt<(select max(loanamt)

from floaa)

Method 4 :

SELECT max(loanamt) FROM(SELECT loanamt FROM floaa

                                 MINUS

                                 SELECT max(loanamt) FROM floaa)

nth (nth) Highest Amount from a Table :

If We change the no (3) in this query it will show the corresponding Record..

SELECT loanamt FROM floaa floaa

WHERE 3 = (SELECT count(DISTINCT(floaa1.loanamt))

FROM floaa floaa1 WHERE floaa.loanamt<= floaa1.loanamt)

nth (nth) Lowest Amount from a Table :

If we need to find 3rd Lowest Amount means, we must replace 1 with 2

SELECT loanamt FROM floaa f1 WHERE 2 =

(SELECT COUNT(DISTINCT loanamt) FROM floaa f2 WHERE f1.loanamt > f2.loanamt)