469,326 Members | 1,541 Online

# Ranking Function 8,127 Expert 4TB
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
0 8393 