CODEWITHSUNDEEP
sql-serversql-order-byunionwhere-clausehsqldb
pauLo_0liveira
pauLo_0liveira

Reputation: 75

ORDER BY with UNION clause

Everywhere I search I find that having something like the code below is supposed to work.

SELECT id, name, age FROM A
UNION
SELECT id, name, age FROM B
ORDER BY name

My query is searching every value on the data base between specific dates, and then it is selecting just a few values before that specified date. By itself this query works, but I want to order it by some column, and I'm getting the error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

This is my code:

DECLARE @inidate DATE = '20201001', @findate DATE = '20201031'

SELECT bo.id, bo.date, bo.status
FROM BO (nolock)
JOIN BO2 (nolock) ON BO2.bo2stamp = BO.bostamp
WHERE (BO.date BETWEEN @inidate AND @findate)
  AND (BO.tabela1 IN ('INSTALED','CANCELED'))

UNION

SELECT bo.id, bo.date, bo.status
FROM BO (nolock)
JOIN BO2 (nolock) ON BO2.bo2stamp = BO.bostamp
WHERE (BO.date < @inidate)
  AND (BO.tabela1 NOT IN ('INSTALED','CANCELED'))
ORDER BY bo2.u_registration_date

I've tried the code shown here, and with it I get an error on ORDER BY

Expected AS, ID, or QUOTED_ID

SELECT * 
FROM
    ([my query])
ORDER BY bo2.u_registration_date

I've also tried

SELECT * 
FROM ([1st query])
UNION 
SELECT * 
FROM ([2nd query])
ORDER BY bo2.u_registration_date

Upvotes: 0

Views: 2377

Answers (1)

GMB
GMB

Reputation: 222632

You would need to put the union in a subquery, and order in the outer query. Also, the subquery needs to return the column that you want to use for sorting.

SELECT *
FROM (
    SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
        FROM BO
        JOIN BO2 ON BO2.bo2stamp = BO.bostamp
        WHERE BO.date BETWEEN @inidate AND @findate AND BO.tabela1 IN ('INSTALED','CANCELED')
    UNION
        SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
        FROM BO
        JOIN BO2 ON BO2.bo2stamp = BO.bostamp
        WHERE BO.date < @inidate AND BO.tabela1 NOT IN ('INSTALED','CANCELED')
) t
ORDER BY u_registration_date

Note that using UNION here does not make a lot of sense. The two members only differ by their date filter, so functionally your query is equivalent to just:

SELECT bo.id, bo.date, bo.status, bo2.u_registration_date
FROM BO
JOIN BO2 ON BO2.bo2stamp = BO.bostamp
WHERE BO.date < @findate AND BO.tabela1 NOT IN ('INSTALED','CANCELED')
ORDER BY bo2.u_registration_date

Upvotes: 4

Related Questions