Tuesday, December 22, 2009

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

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete