Search code examples
sqloracle-databaseplsqlansi-sql

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

Solution

  • 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;