NTH HIGHEST SAL USING SUBQUERY,LEVEL,RANK,DENSE_RANK,ROWNUM

 ====NTH HIGHEST SAL USING SUBQUERY,LEVEL,RANK,DENSE_RANK,ROWNUM=====
==by using sub query==
===2ND HIGHST,3RD,5TH SAL====

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP)--2ND

SQL> SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP);

  MAX(SAL)
----------
      3000

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)) --3RD

SQL> SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP));

  MAX(SAL)
----------
      2975

=====================================================================================================================

==by using functions

SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP


where rank will give the ranking in non-sequence


SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N

SQL> SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;

       SAL        RNK
---------- ----------
      5000          1
      3000          2
      3000          2
      2975          4
      2850          5
      2450          6
      1600          7
      1500          8
      1300          9
      1250         10
      1250         10
      1100         12
       950         13
       800         14
       122         15

15 rows selected.

  1* SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
SQL> /
Enter value for n: 1
old   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=1

       SAL        RNK
---------- ----------
      5000          1

SQL> /
Enter value for n: 2
old   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2

       SAL        RNK
---------- ----------
      3000          2
      3000          2

SQL> /
Enter value for n: 3
old   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3

no rows selected


where dense rank will give the ranking in sequence

SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N

SQL> SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP;

       SAL        RNK
---------- ----------
      5000          1
      3000          2
      3000          2
      2975          3
      2850          4
      2450          5
      1600          6
      1500          7
      1300          8
      1250          9
      1250          9
      1100         10
       950         11
       800         12
       122         13

15 rows selected.

SQL> SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 1
old   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=1

       SAL        RNK
---------- ----------
      5000          1
SQL> SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N;
Enter value for n: 1
old   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=1

       SAL        RNK
---------- ----------
      5000          1

SQL> /
Enter value for n: 2
old   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=2

       SAL        RNK
---------- ----------
      3000          2
      3000          2

SQL> /
Enter value for n: 3
old   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=&N
new   1: SELECT * FROM (SELECT SAL,DENSE_RANK() OVER (ORDER BY SAL DESC)RNK FROM EMP) where RNK=3

       SAL        RNK
---------- ----------
      2975          3


===================================================================================================================

==to get the highest sal based on the parameter by using level==

select max(sal),level from emp where level=&level
 connect by prior sal>sal group by level;
o/p:

SQL> select max(sal),level from emp where level=&level
  2   connect by prior sal>sal group by level;
Enter value for level: 1
old   1: select max(sal),level from emp where level=&level
new   1: select max(sal),level from emp where level=1

  MAX(SAL)      LEVEL
---------- ----------
      5000          1

SQL> /
Enter value for level: 2
old   1: select max(sal),level from emp where level=&level
new   1: select max(sal),level from emp where level=2

  MAX(SAL)      LEVEL
---------- ----------
      3000          2

SQL> /
Enter value for level: 3
old   1: select max(sal),level from emp where level=&level
new   1: select max(sal),level from emp where level=3

  MAX(SAL)      LEVEL
---------- ----------
      2975          3

==to get the minimum sal based on the parameter==

select min(sal),level from emp where level=&level
 connect by prior sal<sal group by level;

o/p:
SQL> select min(sal),level from emp where level=&level
  2   connect by prior sal<sal group by level;
Enter value for level: 1
old   1: select min(sal),level from emp where level=&level
new   1: select min(sal),level from emp where level=1

  MIN(SAL)      LEVEL
---------- ----------
       122          1

SQL> /
Enter value for level: 2
old   1: select min(sal),level from emp where level=&level
new   1: select min(sal),level from emp where level=2

  MIN(SAL)      LEVEL
---------- ----------
       800          2

SQL> /
Enter value for level: 3
old   1: select min(sal),level from emp where level=&level
new   1: select min(sal),level from emp where level=3

  MIN(SAL)      LEVEL
---------- ----------
       950          3



=========================================================================================================
=== INLINE VIEW EXAMPLE==

SELECT MAX(SAL) FROM(SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=20)

SQL> SELECT MAX(SAL) FROM(SELECT ENAME,EMPNO,SAL FROM EMP WHERE DEPTNO=20);

  MAX(SAL)
----------
      3000

==========================================================================================================


===2ND HIGHST,3Rd by using MAX function====

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP)--2ND

SELECT MAX(SAL) FROM EMP  WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)) --3RD


================================================================================================================
SELECT SAL FROM(SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<5

SQL> SELECT SAL FROM(SELECT SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<5 ;

       SAL
----------
      5000
      3000
      3000
      2975

SELECT SAL FROM EMP  WHERE ROWNUM<3 ORDER BY SAL DESC

SQL> SELECT SAL FROM EMP  WHERE ROWNUM<3 ORDER BY SAL DESC;

       SAL
----------
       800
       122

=================================================================================================================