463,134 Members | 621 Online Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

# Ranking Function

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