SQL Server: ORDER BY в подзапросе с UNION

У меня два запроса объединены с 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

Оба из них, конечно, работают отлично по отдельности, но в сочетании с 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

Ошибка запроса с ошибкой:

Msg 104, уровень 15, состояние 1, строка 3
Элементы ORDER BY должны появляться в списке выбора, если оператор содержит оператор UNION.

Как использовать ORDER BY в инструкции с UNION ALL?

Пример с копировальной пастой

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

Сервер: Msg 104, уровень 15, состояние 1, строка 2
Элементы ORDER BY должны появляться в списке выбора, если оператор содержит оператор UNION.

Сноски

¹ Проигранный гипотетический пример. Или нет.

См. также

Ответ 1

Немного взлома, но это сработает.

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

Ответ 2

Собственно, глядя на обходной путь из этой ссылки, о которой я прокомментировал, вы можете попробовать следующее:

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

... или какой-то подобный тип, чтобы попытаться обмануть двигатель, чтобы не жаловаться.

Но я не могу проверить это, я боюсь; Я не думаю, что у нас есть окно 2000, оставшееся в здании, виртуальное или другое.

ИЗМЕНИТЬ: Ах! Похоже, Джо и я наложили на нашу тайну:)

Ответ 3

Я знаю, что вы можете использовать CTE (Common Table Expression), где вы можете использовать свой заказ для 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

Это должно помочь. Важным тигром является удостовериться, что у вас есть ваш заказ по столбцам, возвращаемым во внутреннем запросе (в этом случае CTE).

Сообщите мне, как это работает.