Sorting a list in ORACLE with a result always on TOP

Question

This is my query folks

Select 'A' as Region, To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG 
Union All
Select 'B', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('S','T','F')
Union All
Select 'C', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('E','W','Q')
Union All
Select 'D', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('R','M','G')

I had to create the colomn Region and hard code the values to specify special names for each row and here is the result for this query

The regions here are the A,B,C and D in the code, What I want is to sort them in a special order but having one of the result to be always on the Top, so I tried to add this order by clause

ORDER BY 
    CASE 
       WHEN REGION = 'C' THEN 1 
       ELSE 2
    END

Here's the error I got

For some reason it's not recognizing the values in Region because I'm hard coding the values in this column, Do you guys know a way where I can order by having one result to always show on top using the hard coded value I give to the column? Your help is much appreciated. Thank you

Out of curiosity does it work if you `select * from (your query) der_tbl ORDER BY case when region= 'C' then 1 else 2 end`? this forces the values to materialize and thus region is a defined element when the order is applied. — xQbert, Feb 20 '18 at 21:07
@xQbert - absolutely that would work. The interesting question is, why doesn't it work the way the OP wrote it. It would work if there was no UNION ALL but it doesn't work when the query is a UNION ALL query. — , Feb 20 '18 at 21:12
ahh... the order by is being applied only to the last query. Since region isn't defined on that query we get the error. To apply the order by to the unioned results it would need to be wrapped in a subquery or cte. — xQbert, Feb 20 '18 at 21:17
@xQbert - No, the ORDER BY is never applied only to the last query. This is documented - if you have a query with set operations and an ORDER BY at the end (at the same level), ORDER BY applies to the result of the set operation, **not** to the last operand of the set operation. (Also, in the OP's query, ORDER BY REGION would work just fine; it's the CASE expression that for some reason is not allowed there... I don't see that in the documentation though; not sure why that is.) — , Feb 20 '18 at 21:22
`Select 1 col1, 'z' col2 from dual union all Select 2 , 'y' from dual union all Select 3 , 'x' from dual union all Select 4 , 'w' from dual z order by col2;` fails col2 invalid id `Select 1 col1, 'z' col2 from dual union all Select 2 , 'y' col2 from dual union all Select 3 , 'x' col2 from dual union all Select 4 , 'w' col2 from dual z order by col2;` runs. but I had to alias queries for col2.... odd... — xQbert, Feb 20 '18 at 21:32

Sphinx · Accepted Answer · 2018-02-20T21:23:47.200

1

You need to wrap case when end with the Parentheses.

select * from (
Select 'A' as Region, To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG 
Union All
Select 'B', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('S','T','F')
Union All
Select 'C', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('E','W','Q')
Union All
Select 'D', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('R','M','G')
) order by (case when Region='C' then 1 else 2 end) asc

Or you can create another temp field as SortID, then sort it.

select * from (
Select 2 SortID, 'A' as Region, To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG 
Union All
Select 2 SortID, 'B', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('S','T','F')
Union All
Select 1 SortID, 'C', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('E','W','Q')
Union All
Select 2 SortID, 'D', To_CHAR(TRUNC(max(Date)),'YYYY-MM-DD') As "Incident Date" from DOT.IA_LOG where XX IN ('R','M','G')
) order by SortID asc

edited Feb 20 '18 at 21:23

answered Feb 20 '18 at 21:08

Sphinx

10,519
2
27
45

Thank you the second example worked perfect, I had the same idea and I added a 3rd column and I gave it numbers to sort by, but I had a syntax error so I thought I was doing something wrong but your example showed me that I had to select * from my select statement and then sort by the created column, Thank you for your help – Michael B Feb 20 '18 at 21:20
The first solution is incorrect - you cannot guarantee the order from a UNION ALL query unless you have an explicit ORDER BY. Just because it seems to give the correct order in a small test case it doesn't mean you can rely on it working in much larger cases. – Feb 20 '18 at 21:21
@mathguy, you are correct. Thanks. [Does UNION ALL guarantee the order of the result set](https://stackoverflow.com/questions/15766359/does-union-all-guarantee-the-order-of-the-result-set). Before I thought `Union All` has default sort. – Sphinx Feb 20 '18 at 21:25
The link you provided is for SQL Server, and it is entirely possible that SQL Server and Oracle do certain things in different ways. In this case, though, that answer would apply to Oracle also. – Feb 20 '18 at 21:26
1

@mathguy, Yes, the key point should be the result of a UNION or UNION ALL is a set. Sets do not have an order. – Sphinx Feb 20 '18 at 21:31

Sorting a list in ORACLE with a result always on TOP

1 Answers1