ROW_NUMBER, RANK, DENSE_RANK IN ORACLE

Top-n Query or 3th highest row by sql quiery in oracle .

Using ROW_NUMBER, RANK, DENSE_RANK

=====================================

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 13 13:06:27 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> break on deptno skip 1
SQL>
SQL> select deptno, ename,sal,
2 row_number()
3 over (partition by deptno
4 order by sal desc)rn,
5 rank()
6 over (partition by deptno
7 order by sal desc)rnk,
8 dense_rank()
9 over (partition by deptno
10 order by sal desc)drnk
11 from emp
12 order by deptno, sal desc
13 /

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
10 KING 5000 1 1 1
CLARK 2450 2 2 2
MILLER 1300 3 3 3

20 SCOTT 3000 1 1 1
FORD 3000 2 1 1
JONES 2975 3 3 2
ADAMS 1100 4 4 3
SMITH 1000 5 5 4

30 BLAKE 2850 1 1 1

DEPTNO ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ---------- ----------
30 ALLEN 1600 2 2 2
TURNER 1500 3 3 3
MARTIN 1250 4 4 4
WARD 1250 5 4 4
JAMES 950 6 6 5


14 rows selected.

SQL>
SQL> select ename,sal,
2 row_number()
3 over (order by sal desc)rn,
4 rank()
5 over (order by sal desc)rnk,
6 dense_rank()
7 over (order by sal desc)drnk
8 from emp
9 order by sal desc
10 /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
ALLEN 1600 7 7 6
TURNER 1500 8 8 7
MILLER 1300 9 9 8
WARD 1250 10 10 9
MARTIN 1250 11 10 9

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
ADAMS 1100 12 12 10
SMITH 1000 13 13 11
JAMES 950 14 14 12

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=5
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
BLAKE 2850 5 5 4

SQL> ed
Wrote file afiedt.buf

1 select ename,sal,rn,rnk,drnk
2 from (
3 select ename,sal,
4 row_number()
5 over (order by sal desc)rn,
6 rank()
7 over (order by sal desc)rnk,
8 dense_rank()
9 over (order by sal desc)drnk
10 from emp
11 order by sal desc
12 )
13* where rn=3
SQL>
SQL>
SQL> /

ENAME SAL RN RNK DRNK
---------- ---------- ---------- ---------- ----------
FORD 3000 3 2 2

SQL>
SQL>
SQL>