SQL Analytical Functions - RANK, DENSE RANK (rank the data)
Rank:
Used to rank the records.
Employees -Table
EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 24000 101 Neena 17000 102 Lex 17000 103 Alexander 9000 104 Bruce 6000 105 David 4800 106 Valli 4800 107 Diana 4200
Below query ranks the records based on salary. If 2 employees have the same salary, the same rank will be given to both the employees. The next rank assigned will not be consecutive (as highlighted below.)
select e.employee_id, e.first_name as name, e.department_id, salary, rank() over (partition by department_id order by salary) as rank from employees e;
Partition by- group the records by department
Order by salary - sort the data in groups with the salary
rank() - rank the employees of each department with the salary
EMPLOYEE_ID NAME DEPARTMENT_ID SALARY RANK ----------- -------------------- ------------- ---------- ---------- 107 Diana 60 4200 1 106 Valli 60 4800 2 105 David 60 4800 2 104 Bruce 60 6000 4 103 Alexander 60 9000 5 101 Neena 90 17000 1 102 Lex 90 17000 1 100 Steven 90 24000 3
DENSE_RANK:
Dense rank also works similarly as rank. The only difference is when the same rank is given to multiple records, then the next rank assigned will be consecutive (as highlighted below.)
select e.employee_id, e.first_name as name, e.department_id, salary, rank() over (partition by department_id order by salary) as rank from employees e;
EMPLOYEE_ID NAME DEPARTMENT_ID SALARY RANK ----------- -------------------- ------------- ---------- ---------- 107 Diana 60 4200 1 106 Valli 60 4800 2 105 David 60 4800 2 104 Bruce 60 6000 3 103 Alexander 60 9000 4 101 Neena 90 17000 1 102 Lex 90 17000 1 100 Steven 90 24000 2
Comments
Post a Comment