SQL Server: ORDER BY in subquery with UNION
i have two queries being combined with a UNION ALL
1:
--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 Fr开发者_开发问答iends
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
- 1Contrived 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
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
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.
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 :)
I'm suggesting to create a variable table in the format of the columns you want.
- 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.
- 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
精彩评论