SQL Server: ORDER BY in subquery with UNION

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