 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
# 1
Jun 30 '07
| |
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
========== - select ename,empno ,sum(sal) from emp where deptno=10 group by ename,empno order by ename,empno;
Ex #2
========== -
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
========== - 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
========== - 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
========== - 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
========== - 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
----------------------------------- -
select DEPTNO,empno ,sum(sal) SALARY,
-
rank() over (order by sum (sal) desc NULLS LAST) RANK
-
from emp group by DEPTNO,empno;
-
Last edited by debasisdas; Jan 22 '08 at 07:31 AM.
Reason: aded some code
|
