how to express DENSE_RANK with ANSI SQL?
Is it possible to express the statment below by ANSI SQL? Since the example below belongs to PL/SQL. Thanks in advance.
SELECT department_id, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best" FROM employees GROUP BY department_id; DEPARTMENT_ID Worst Best ------------- ---------- ---------- 10 4400 4400 20 6000 13000 30 2500 11000 40 6500 6500 50 2100 8200 60 4200 9000 70 10000 10000 80 6100 14000 90 17000 24000 100 6900 12000 110 8300 12000 7000 7000
This returns the same result (as far as I can tell) but does not need a join and is ANSI SQL:
select department_id, min(case when min_comm = 1 then salary end) as worst, max(case when max_comm = 1 then salary end) as best from ( select department_id, salary, dense_rank() over (partition by department_id order by commission_pct desc) as max_comm, dense_rank() over (partition by department_id order by commission_pct) as min_comm from employees ) t group by department_id order by 1;
You can use self-joins instead of analytics in most cases. Here's an equivalent of your MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct):
SELECT department_id, MAX(salary) FROM employees e WHERE (department_id, commission_pct) IN (SELECT department_id, MAX(commission_pct) FROM employees GROUP BY department_id) GROUP BY department_id
Obviously getting both the MAX and MIN would be a bit trickier (and uglier) but is doable.
This construct avoids additional join to employees table. In ANSI SQL you would have to 1st query the highest commission_pct per department and then join the employees (again) to find salaries which have this highest commission_pct
In ANSI SQL it would like something like this:
select * from ( SELECT department_id, MIN(commission_pct) max_c MAX(commission_pct) min_c FROM employees GROUP BY department_id ) e1 join employees e2 on (e1.department_id = e2.department_id and e1.max_c = e2.commission_pct) join employees e3 on (e1.department_id = e3.department_id and e1.min_c = e3.commission_pct)
Even this is not 100% correct.
Having given this some more thought, I'll take a stab at it. I think you are trying to show the highest and lowest value (salary in the example) for each group(deptid) using ANSI SQL? Here's a super-simplistic example of it (using DENSE_RANK()) that should work on any ANSI compliant db:SQL FIddle
select t1.deptid, t1.salary as Highest, t2.salary as Lowest from ( select deptid, salary, dense_rank() over (partition by deptid order by salary desc) as First --rank desc for highest from salaries ) T1 inner join ( select deptid, salary, dense_rank() over (partition by deptid order by salary asc) as worst -- rank ascending for lowest from salaries )T2 on t1.deptid = t2.deptid where t1.first = 1 --GET THE HIGHEST SALARY and t2.worst = 1 -- GET THE LOWEST
Using min and max would also work and be a lot simpler:Another SQL Fiddle
select deptid, max(salary) as Highest, min(salary) as Lowest from salaries group by deptid