Get N Max Value. RANK(), DENSE_RANK(), PARTITION BY

(Get Max 3 Salaries)

Using Sub-Query:

SELECT *
  FROM EMP
 WHERE SAL = (SELECT MAX(SAL) FROM EMP)
UNION
SELECT *
  FROM EMP
 WHERE SAL =  (SELECT MAX(SAL)
                              FROM EMP
                             WHERE SAL < (SELECT MAX(SAL) FROM EMP))
UNION
SELECT *
  FROM EMP
 WHERE SAL = (SELECT MAX(SAL)
                FROM EMP
               WHERE SAL < (SELECT MAX(SAL)
                              FROM EMP
                             WHERE SAL < (SELECT MAX(SAL) FROM EMP)))
                             
ORDER BY 6 DESC  ;

It’s an easily achievable using by DENSE_RANK , RANK function. 

Using DENSE_RANK()

SELECT EMPNO, ENAME, SAL
  FROM (SELECT EMPNO,
               ENAME,
               SAL,
               DENSE_RANK() OVER(ORDER BY SAL DESC NULLS LAST) TOP_MOST
          FROM EMP)

 WHERE TOP_MOST < 4

Using RANK()

 SELECT EMPNO, ENAME, SAL

  FROM (SELECT EMPNO,

               ENAME,

               SAL,

               RANK() OVER(ORDER BY SAL DESC NULLS LAST) TOP_MOST

          FROM EMP)

 WHERE TOP_MOST < 5

To understand the better difference b/w RANK() and DENSE_RANK(), Check the below mention query:

SELECT EMPNO,

       ENAME,

       SAL,

       RANK() OVER(ORDER BY SAL DESC NULLS LAST) RANK,

       DENSE_RANK() OVER(ORDER BY SAL DESC NULLS LAST) DENSE_RANK

  FROM EMP