CODEWITHSUNDEEP
sqloracle-database
TangoKilo
TangoKilo

Reputation: 1785

How can I order a SQL query that uses UNION so that it returns the data in the order that the queries are in?

I have a SQL Query along the following lines:

SELECT R.NUMERIC_VAL, 'A'  /* query A */
  FROM TABLE_1  
 WHERE TABLE_1.DATE = TO_DATE('04/04/2012', 'DD/MM/YYYY') 
 UNION
SELECT E.NUMBER_VALUE, 'B' /* query B */
  FROM TABLE_2 
 WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
 UNION
SELECT E.OTHER_NUMBER_VALUE, 'C' /* query C */
  FROM TABLE_2
 WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
 ORDER BY 2

As you can see, my current solution fetches back the letters A, B and C and orders the query by the second column. I know for a fact that this query will always bring back 1 row per query. I was wondering if there was any way of getting the query to bring back the data in the order that the queries are currently written in, without having to use the second column? Thanks.

Upvotes: 2

Views: 560

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You have to use order by to get the results in the order that you want. SQL tables and SQL results are inherently unordered when there is no order by, except for a few database-specific exceptions (for instance, MySQL guarantees the ordering when using group by).

UNION and UNION ALL do not guarantee the ordering of the results. In fact, in many databases, UNION will return the results in alphabetical order (by the first column) as part of the duplicate removal process.

You can replace the UNION with UNION ALL. Although this is not guaranteed to return the results in a particular order, it will typically return them in the order written:

SELECT R.NUMERIC_VAL, 'A'  /* query A */
FROM TABLE_1  
WHERE TABLE_1.DATE = TO_DATE('04/04/2012', 'DD/MM/YYYY') 
UNION ALL
SELECT E.NUMBER_VALUE, 'B' /* query B */
FROM TABLE_2 
WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
UNION ALL
SELECT E.OTHER_NUMBER_VALUE, 'C' /* query C */
FROM TABLE_2
WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')

However, I would advise you to keep the ORDER BY.

If you don't want the second column, use a subquery:

select NUMERIC_VAL
from (SELECT R.NUMERIC_VAL, 'A' as ordering /* query A */
      FROM TABLE_1  
      WHERE TABLE_1.DATE = TO_DATE('04/04/2012', 'DD/MM/YYYY') 
      UNION ALL
      SELECT E.NUMBER_VALUE, 'B' /* query B */
      FROM TABLE_2 
      WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
      UNION ALL
      SELECT E.OTHER_NUMBER_VALUE, 'C' /* query C */
      FROM TABLE_2
      WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
     ) t
ORDER BY ordering;

Upvotes: 5

Janick Bernet
Janick Bernet

Reputation: 21184

SELECT NumVal FROM
(
    SELECT R.NUMERIC_VAL AS NumVal, 1 AS queryNo
    FROM TABLE_1  
    WHERE TABLE_1.DATE = TO_DATE('04/04/2012', 'DD/MM/YYYY') 
    UNION
    SELECT E.NUMBER_VALUE AS NumVal, 2 AS queryNo
    FROM TABLE_2 
    WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
    UNION
    SELECT E.OTHER_NUMBER_VALUE AS NumVal, 3 AS queryNo
    FROM TABLE_2
    WHERE TABLE_2.DATE = TO_DATE('05/04/2012', 'DD/MM/YYYY')
) T
ORDER BY queryNo ASC

Though I don't see a way to do this reliably without the fake 2nd column, but this way at least it's not part of the result. Also you might just try using UNION ALL, which means that it does not have to eliminate duplicate values, likely preserving order, but as I said, probably not reliably.

Upvotes: 5

Related Questions