CODEWITHSUNDEEP
sqloracle-databasesortingpartition-by
dr jerry
dr jerry

Reputation: 10026

SQL how to order on highest rank in group, with total group

dataset taken from: Tim Hall's Oracle-Base rank/partition documentation Original assignment was to rank the salaries within a department. The MYRANK column is syntactic and introduced with 

RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank

But now I want to sort on the highest salary followed with all records within the same department. Then the second highest salary again followed with all the records in the same department. The order of dept is a kind of coincidental, it happens to be aligned with the order of highest salary per departnement.

I think I can solve this when the rank() is substituted for a max() like:

MAX() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS MAX

and than a order by MAX, DEPTNO, but this fails with: invalid number of arguments

EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------

7839         10       5000          3
7782         10       2450          2
7934         10       1300          1
7788         20       3000          4
7902         20       3000          4
7566         20       2975          3
7876         20       1100          2
7369         20        800          1
7698         30       2850          6
7499         30       1600          5
7844         30       1500          4
7654         30       1250          2
7521         30       1250          2
7900         30        950          1

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can put analytic functions in the order by, so you can do:

order by max(sal) over (partition by deptno) desc,
         deptno,
         sal desc

Notice that this has three keys in the order by. The deptno is needed in case two departments have the same highest salary.

Upvotes: 2

Related Questions