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

School

Your example works for me in SQL Server 2005 even if I set the database to SQL2000 compatibility mode. – Caravan

Looks fine to me. Wonder if there was an odd restriction or bug in SQL Server 2000? I can't remember, but hey, it's been nearly a decade... – Durbin

There are other ways to write this query. Are you just trying to solve this specific problem or do you only want solutions that allow you to use the subquery? – Hackneyed

@Martin: I can duplicate the issue on a real SQL 2000 instance. – Reddick

@Tom H. i'm perfectly willing to let someone else do the work and rewrite the query. Keep in mind that the real query that triggered this question is much more complicated - which makes it that much more difficult to convert. Certainly re-writing the query will be an up-voted answer, that other people having the same issue might be able to do (and perhaps we can use also). But it's looking like the valid answer is going to have to say, "This is a bug in the optimizer in SQL Server 2000, that was fixed in 2005. And your only way around it is to re-write the query.". – School

In fact, put that exact statement, with your re-write, and you have yourself an accepted answer. – School

Looks like you may be running into this: connect.microsoft.com/SQLServer/feedback/details/421924/… <-- Expand the details. It's not a lot of help, I don't think, but it may at least confirm it's a bug with 2000 that MS never fixed, so patches and service packs won't help. – Durbin

Answer 1

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

Reddick

Answer 2

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.

Gaullist

He's on SQL 2000. No CTE support. – Caravan

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 :)

Durbin

Answer 4

I'm suggesting to create a variable table in the format of the columns you want.

1. run insert query from origin table into variable table for each table you which to join including all filters and sorting you want to apply.
2. Return the Variable table

Example:

set nocount on
DECLARE @temp_table TABLE(Flavor varchar(20), Color varchar(20))
    
    insert into @temp_table (Flavor,Color)
    /*Apply select query #1 with all filters, joins and sorting */
    SELECT Flavor,Color   FROM Strangers  ORDER BY Wavelength DESC
    
    insert into @temp_table (Flavor,Color)
    /*Apply select query #2 with all filters, joins and sorting */
    SELECT Flavor, Color FROM Friends
    
    /*Return the results pushed into @variable table */
    select * from @temp_table

Stomato