Use Analytic Functions
SELECT e.name as emp_name, d.name dept_name, e.salary,
ROW_NUMBER() OVER(Partition by d.name ORDER BY e.salary DESC) AS row_num
FROM employee e join department d on e.dept_id = d.dept_id
SELECT e.name as emp_name, d.name dept_name, e.salary,
RANK() OVER(Partition by d.name ORDER BY e.salary DESC) AS rank
FROM employee e join department d on e.dept_id = d.dept_id
SELECT e.name as emp_name, d.name dept_name, e.salary,
DENSE_RANK() OVER(Partition by d.name ORDER BY e.salary DESC) AS dense_rank
FROM employee e join department d on e.dept_id = d.dept_id
Without Using Analytic Function
select emp.*,
(select count(*)
from employee emp2
where emp2.dept_id = emp.dept_id
and(emp2.salary > emp.salary or emp2.salary = emp.salary
and emp2.emp_id <= emp.emp_id
)
) as "row_number",
(select 1 + count(*)
from employee emp2
where emp2.dept_id = emp.dept_id and emp2.salary > emp.salary
) as "rank",
(select count(distinct salary)
from employee emp2
where emp2.dept_id = emp.dept_id and emp2.salary >= emp.salary
) as "dense_rank"
from employee emp;
No comments:
Post a Comment