CODEWITHSUNDEEP
sql-serverunion
F.Rocco
F.Rocco

Reputation: 21

T-SQL UNION between two queries with different order by, all orded by

As the title I have this problem:

I have these two queries

SELECT state, TIMESTAMP
FROM myTable
WHERE state != 9
ORDER BY TIMESTAMP ASC

SELECT TOP (3) state, TIMESTAMP
FROM myTable
WHERE state = 9
ORDER BY TIMESTAMP DESC

is it possible to ORDER BY again (ORDER BY TIMESTAMP ASC) the UNION of these queries?

Thank you

EDIT: to be more specific

I have a table with [state] and [timestamp] fields. [State] value can be 3, 8 or 9. What I need is to select:
[state] = 9 last 3 records
[state] != 9 all

all ORDER BY [timestamp] ASC

Upvotes: 1

Views: 95

Answers (2)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

Please try like this

SELECT  *
FROM    (
    SELECT state, TIMESTAMP
    FROM myTable
    WHERE state != 9
    UNION
    SELECT TOP (3) state, TIMESTAMP
    FROM myTable
    WHERE state = 9
    ORDER BY (CASE WHEN state = 9 THEN TIMESTAMP END ) DESC
        ,(CASE WHEN state != 9 THEN TIMESTAMP END ) ASC
)   D

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

You can do this:

SELECT [state], [TIMESTAMP]
FROM
(
    SELECT [state], [TIMESTAMP]
    FROM   myTable
    WHERE  [state] != 9   

    UNION 

    SELECT TOP(3) [state], [TIMESTAMP]
    FROM          myTable
    WHERE         [state] = 9
    ORDER BY [TIMESTAMP] DESC
) u
ORDER BY [TIMESTAMP] ASC

The order by of the first query is redundant in the union, but the second query needs it for the top(3), otherwise you get 3 arbitrary rows.

Upvotes: 3

Related Questions