DISTINCT
actually filtered the UNIQUE content in the result set, with whatever expressions given in the SELECT clause.
We cannot order it using a Different expression or column name. Please see the example here.
SQL> l
1 SELECT DISTINCT (col1),(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
COL1 COL2
----- -----
HELLO WORLD
Hello World
You can see that DISTINCT
is CASE SENSITIVE
here.(2 rows displayed)
So, let me Do a UPPER()
on both columns.
SQL> l
1 SELECT DISTINCT UPPER (col1),UPPER(col2)
2 FROM
3 ( SELECT 'Hello' col1,'World' col2 FROM DUAL
4 UNION ALL
5 SELECT 'HELLO','WORLD' FROM DUAL
6* )
SQL> /
UPPER UPPER
----- -----
HELLO WORLD
Just 1 row is Displayed, ignoring the case.
Coming back to the actual problem. To order something on a DISTINCT
Resultset, it has to be a part of DISTINCT
clause's expression/column.
So, When you issue DISTINCT COL1,COl2
, the order by may be by COL1
or COL2
/.. it cannot be COL3
or even UPPER(COL1)
because UPPER() makes a different expression conflicting the expression over DISTINCT.
Finally, Answer for your Question would be
if you want your ORDER
to be case-insensitive, DISTINCT
also has to the same way! As given below
SELECT DISTINCT
UPPER(asssss),
saas_acc
FROM DUAL
ORDER BY upper(asssss) ASC ;
OR if UNION has to be used, better do this, or same as above one.
SELECT * FROM
(
SELECT DISTINCT asssss as asssss,
saas_acc
FROM DUAL
UNION
SELECT '--ALL--','ALL' FROM DUAL
)
ORDER BY upper(asssss) ASC ;
Out of my own Experience, I had always felt, what ever expression/column is specified in the ORDER BY
, it is implicitly taken to final SELECT
as well. Ordering is just based on the column number(position) in the result actually . In this situation, DISTINCT COL1,COl2
is already there. When you give ORDER BY UPPER(COL1)
, it will be tried to append into the SELECT
expression, which is NOT
possible at all. So, Semantic check itself, would disqualify this query with an Error!