Taking example from Book Advance SQL Functions In Oracle 10g
create table employee( empno number(3),
ename varchar2(20),
hiredate date,
orig_salary number(6),
curr_salary number(6),
region varchar2(2))
insert into employee
values(106,'Chloe','19-jan-96',33000,44000,'W');
insert into employee
values(101,'John','02-dec-97',35000,44000,'W');
insert into employee
values(102,'Stephanie','22-sep-98',35000,44000,'W');
insert into employee
values(108,'David','08-jul-01',37000,39000,'E');
insert into employee
values(106,'Lindsey','22-may-97',30000,52000,'E');
insert into employee
values(106,'Christina','08-mar-98',43000,55000,'W');
insert into employee
values(111,'Katie','13-apr-00',45000,49000,'E');
fuction() over(
)
row_number
**********
SQL> select empno,ename,orig_salary,row_number() over(order by orig_salary desc )
toprank from employee;
EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4 <<<<<----
102 Stephanie 35000 5 <<<<<----
106 Chloe 33000 6
106 Lindsey 30000 7
7 rows selected.
It will rank the same value salary .
rank
**********
SQL> select empno,ename,orig_salary,rank() over(order by orig_salary desc ) toprank
from employee;
EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4 <<<<<----
102 Stephanie 35000 4 <<<<<----
106 Chloe 33000 6 *****----
106 Lindsey 30000 7
7 rows selected.
*****----
it will rank the ORIG_SALARY same, but will consume next sequence .
dense_rank
**********
SQL> select empno,ename,orig_salary,dense_rank() over(order by orig_salary desc )
toprank from employee;
EMPNO ENAME ORIG_SALARY TOPRANK
---------- -------------------- ----------- ----------
111 Katie 45000 1
106 Christina 43000 2
108 David 37000 3
101 John 35000 4
102 Stephanie 35000 4
106 Chloe 33000 5
106 Lindsey 30000 6
7 rows selected.
No comments:
Post a Comment