Implement Rank without using analytic function
11,692
Solution 1
Here are the three equivalent expressions:
select emp.*,
(select count(*)
from emp 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 emp emp2
where emp2.dept_id = emp.dept_id and
emp2.salary > emp.salary
)
) as "rank",
(select count(distinct salary)
from emp emp2
where emp2.dept_id = emp.dept_id and
emp2.salary >= emp.salary
) as "dense_rank",
from emp;
This assumes the existence of an emp_id
to make the rows unique for "row_number".
Solution 2
You can do this with a correlated sub-query.
select dept_id,salary,
(select count(*) from emp e1 where e1.dept_id=e.dept_id and e1.salary>=e.salary) as rnum
from emp e
This works well when there are no ties.
Solution 3
This would work for all cases
select DEPT_ID, SALARY,
(select count(*)+1 from emp r where r.SALARY>o.SALARY and r.dept_id=o.dept_id) **rank**,
(select count(distinct SALARY )+1 from emp r where r.SALARY>o.SALARY and r.dept_id=o.dept_id) *d_rank*,
(select count(*)+1 from (select x.*,rownum rn from ( select emp.* from emp order by DEPT_ID asc,salary desc ) x) r where r.rn<o.rn and r.dept_id=o.dept_id) **rownumm**
from (select x.*,rownum rn from ( select emp.* from emp order by DEPT_ID asc,salary desc ) x) o
order by DEPT_ID,salary desc;
for rank:- calculated using (count of (values less than current rows)+1
for dense rank:- same as rank (count distinct value less than current rows)+1
row_number:- create the nested query by generating rownum for each row which will be distinct for all rows. Now on top of that do the same logic as rank (count of values greater than previous rownum (rownum of select subquery))+1
Author by
Teja
Updated on July 03, 2022Comments
-
Teja almost 2 years
I am wondering if there is a method to implement SQL analytic functions without using the inbuilt functions.
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rownum, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS denserank, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM emp;