Skip to content
Advertisement

Order By in Union Nested selects

I have two really long selects that are both nested

(SELECT MAX(u.Username) as Identification,max(cht.SentOn) NewestMessage 
from Chats cht 
JOIN(some other select as u that has u.username))

union 

(select Max(GC.Identification)Identification,Min(cht.SentOn) NewestMessage 
from Chats cht 
join(some other select as GC that has GC.Identification))

How can I order these (both queries combined into one table results) by NewestMessage which is a type of datetime?

Advertisement

Answer

I would slightly alter your query to:

SELECT Identification, NewestMessage
FROM
(
    SELECT MAX(u.Username) AS Identification, MAX(cht.SentOn) AS NewestMessage
    FROM Chats cht 
    INNER JOIN (some other select as u that has u.username)
    UNION   -- maybe UNION ALL if you don't mind duplicates?
    SELECT MAX(GC.Identification), MIN(cht.SentOn)
    FROM Chats cht
    INNER JOIN (some other select as GC that has GC.Identification)
) t
ORDER BY NewestMessage

I am basically replacing your union of tuples to a subquery on a union query, using an ORDER BY with the column you want. Note that aliases in the second half of the union query are not necessary, and in fact will be ignored by SQL Server.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement