SQL Server: ORDER BY in subquery with UNION

Question

i have two queries being combined with a UNION ALL¹:

--Query 1
SELECT Flavor, Color
FROM Friends

 

--Query 2
SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers

Both of which, of course, work fine separately, but when combined with a UNION ALL:

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers

The query fails with the error:

Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains a UNION operator.

How do i use an ORDER BY in a statement with a UNION ALL?

Copy-Pasteable Example

CREATE TABLE Friends (Flavor int, Color int)
CREATE TABLE Strangers (Flavor int, StrangerID int)
CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)
go

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT TOP 1 Color
     FROM Rainbows
     WHERE Rainbows.StrangerID = Strangers.StrangerID
     ORDER BY Wavelength DESC
    ) AS Color
FROM Strangers
go

DROP TABLE Rainbows
DROP TABLE Strangers
DROP TABLE Friends

Server: Msg 104, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Footnotes

• ¹Contrived hypothetical example. Or not.

See also

• SQL Query - Using Order By in UNION
• SQL UNION and ORDER BY
• SQL: Using Top 1 in UNION query with Order By
• Impact of ordering of correlated subqueries within a projection

Answer 1

I know that you could use a CTE (Common Table Expression) , where you can use your order by for the CTE.

;with results as
(

    SELECT Cassettes.CassetteNumber,
    LastCassetteTransfers.Amount,
    CassetteTransfers.CreatedDate
    FROM Cassettes
    INNER JOIN LastCassetteTransfers
    ON Cassettes.CassetteGUID = LastCassetteTransfers.CassetteGUID

    UNION ALL

    SELECT Cassettes.CassetteNumber,
    (
       SELECT TOP 1 CassetteTransfers.Amount
       FROM CassetteTransfers
       WHERE CassetteTransfers.CassetteGUID = Cassettes.CassetteGUID
       AND CassetteTransfers.Mode = 'ctmLoad'
    ) AS Amount,
    CassetteTransfers.CreatedDate
    FROM Cassettes

)

SELECT CassetNumber, Amount
FROM results
ORDER BY CassetteTransfers.CreatedDate DESC, CassetteTransfers.Amount

That should help. The important thig is to make sure that you have your order by columns returned in the inner query (in this case the CTE).

Let me know how it works.

Answer 2

A bit of a hack, but this will work.

CREATE TABLE Friends (Flavor int, Color int)
CREATE TABLE Strangers (Flavor int, StrangerID int)
CREATE TABLE Rainbows (StrangerID int, Color int, Wavelength int)
go

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
    (SELECT Color FROM 
        (SELECT TOP 1 Color, Wavelength
         FROM Rainbows
         WHERE Rainbows.StrangerID = Strangers.StrangerID
         ORDER BY Wavelength DESC
         ) AS Foo
    ) AS Color
FROM Strangers
go

DROP TABLE Rainbows
DROP TABLE Strangers
DROP TABLE Friends

Answer 3

Actually, looking at the workaround from that link I commented, you might want to try this:

SELECT Flavor, Color
FROM Friends

UNION ALL

SELECT Flavor,
(SELECT TOP 1 Color FROM 
    (SELECT Color, Wavelength
    FROM Rainbows
    WHERE Rainbows.StrangerID = Strangers.StrangerID
) X ORDER BY Wavelength DESC) AS Color
FROM Strangers

...or some similar type of thing to try to fool the engine into not complaining.

But I can't test it, I'm afraid; I don't think we've got a 2000 box left in the building, virtual or otherwise.

EDIT: Ah! Looks like Joe and I overlapped on our sneakiness :)