RANK(), DENSE_RANK(), PARTITION BY ...

1. How to get the N th Max value?
    (Everybody knows this question in another form - query for the 2nd max salary)

If N = 2

SELECT MAX (sal)
FROM emp
WHERE sal < (SELECT MAX (sal)
FROM emp);
 
If N = 4 / 5 or n ...
It’s an easily achievable using by DENSE_RANK function.
 
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;

2. What is the difference between RANK and DENSE_RANK function?

       (For better understanding; check the below queries and outputs.)

SELECT * FROM emp;

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;

3. Example for PARTITION BY

SELECT ename, sal, RANK () OVER (ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK () OVER (ORDER BY sal DESC NULLS LAST) DENSE_RANK, deptno, DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) deptno_rank
FROM emp
ORDER BY deptno, deptno_rank