By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,529 Members | 864 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Ranking Function

debasisdas
Expert 5K+
P: 8,127
Rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values,in Which Case The Same Ranking Is Assigned And A Gap Appears In The Sequence For Each Duplicate Ranking.

Row_number:-returns A Unique Number For Each Row Starting With 1.for Rows That Have Duplicate Values,numbers Are Arbitarily Assigned.

Dense_rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values ,in Which Case Same Ranking Is Assigned.

Ex #1
==========

Expand|Select|Wrap|Line Numbers
  1. select ename,empno ,sum(sal)  from emp where deptno=10 group by ename,empno order by ename,empno;
Ex #2
==========

Expand|Select|Wrap|Line Numbers
  1. select ename,empno ,sum(sal) SALARY,
  2. rank() over(order by sum (sal) desc) RANK,
  3. dense_rank() over(order by sum (sal) desc) SALARY_DENSE,
  4. ROW_NUMBER() OVER (order by sum (sal) desc) SALARY_NUMBER
  5. from emp
  6. where deptno=10
  7. group by ename,empno
  8. order by ename,empno;
Ex #3
==========
Expand|Select|Wrap|Line Numbers
  1. select ename,empno ,sum(sal) SALARY,
  2. rank() over (PARTITION BY ENAME order by sum (sal) desc) RANK,
  3. dense_rank() over (PARTITION BY ENAME order by sum (sal) desc) SALARY_DENSE,
  4. ROW_NUMBER() OVER (PARTITION BY ENAME order by sum (sal) desc) SALARY_NUMBER
  5. from emp
  6. group by ename,empno
  7. order by ename,empno;
Ex #4
==========
Expand|Select|Wrap|Line Numbers
  1. select DEPTNO,empno ,sum(sal) SALARY,
  2. rank() over (PARTITION BY DEPTNO order by sum (sal) desc) RANK,
  3. dense_rank() over (PARTITION BY DEPTNO order by sum (sal) desc) SALARY_DENSE,
  4. ROW_NUMBER() OVER (PARTITION BY DEPTNO order by sum (sal) desc) SALARY_NUMBER
  5. from emp
  6. WHERE DEPTNO=30
  7. group by DEPTNO,empno
  8. order by DEPTNO,empno;
Ex #5
==========
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. MIN(DEPTNO)
  3. KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) BEST,
  4. MIN(DEPTNO)
  5. KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) WORST
  6. FROM EMP
  7. GROUP BY DEPTNO;
Ex #6
==========
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. MIN(DEPTNO)
  3. KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) MIN_BEST,
  4. MAX(DEPTNO)
  5. KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) MAX_BEST,
  6. MIN(DEPTNO)
  7. KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) MIN_WORST,
  8. MAX(DEPTNO)
  9. KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) MAX_WORST
  10. FROM EMP
  11. GROUP BY DEPTNO;
HANDLING NULLS
-----------------------------------
Expand|Select|Wrap|Line Numbers
  1. select DEPTNO,empno ,sum(sal) SALARY,
  2. rank() over (order by sum (sal) desc NULLS LAST) RANK
  3. from emp group by DEPTNO,empno;
  4.  
Jun 30 '07 #1
Share this Article
Share on Google+