Hi,
The SQL Language manual
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#i2171079
says
Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:
If you have specified the DISTINCT operator in this statement, then this clause cannot refer to columns unless they appear in the select list.
It doesn't say that only columns in the select list can appear in ORDER BY clause. I tried some expressions that were entirely dependent on the columns in the SELECT clause. Sometimes they worked, for example:
SELECT DISTINCT deptno, job -- This is Query 1
FROM scott.emp
ORDER BY deptno
, SUBSTR (job, 2)
;
Output:
DEPTNO JOB
---------- ---------
10 MANAGER
10 CLERK
10 PRESIDENT
20 MANAGER
20 CLERK
20 ANALYST
30 SALESMAN
30 MANAGER
30 CLERK
but sometimes they didn't work. For example:
SELECT DISTINCT deptno, job -- This is Query 2
FROM scott.emp
ORDER BY deptno
, MIN (deptno) OVER (PARTITION BY job) -- DESC
;
Output:
DEPTNO JOB
---------- ---------
10 PRESIDENT
10 CLERK
10 MANAGER
20 ANALYST
20 MANAGER
20 CLERK
30 SALESMAN
30 MANAGER
30 CLERK
Here, I expected 'CLERK' and 'MANAGER' to come first in departments 20 and 30, because those jobs are also found in department 10.
Adding DESC to the 2nd ORDER BY expression didn't change the output.
Am I missing somehting? Can anyone explain why some expressions work in the ORDER BY clause, but other don't?
This output was taken from Oracle 12.1.0.1.1, but similar results occur in other versions.
This question was inspired by the following thread:
https://community.oracle.com/thread/3581684
Thanks.