CODEWITHSUNDEEP
sql-server
bonny
bonny

Reputation: 738

How to use ORDER BY along with UNION ALL in SQL Server?

I have 3 tables which I need to combine using UNION ALL.

Assume that table (T1,T2,T3) contains columns as follows:

Table T1

Name, Qty, Amt, EXP

Table T2

Qty, Amt, EXP, SRNO

Table T3

Name, Qty, EXP

My query is something like this:

SELECT Name, Qty, Amt, EXP 
FROM T1

UNION ALL

SELECT NULL AS Name, Qty, Amt, EXP 
FROM T2 
ORDER BY SRNO

UNION ALL

SELECT Name, Qty, NULL Amt, EXP 
FROM T3

I also tried

SELECT Name, Qty, Amt, EXP
FROM T1

UNION ALL

SELECT Name, Qty, Amt, Exp 
FROM 
    (SELECT * 
     FROM T2 
     ORDER BY SRNO) AS T21

UNION ALL

SELECT Name, Qty, NULL Amt, EXP
FROM T3

The result from T2 is uneven and I want to sort it by SRNO(integer) but I get errors when using unions.

NOTE I don't want to take SRNO in UNION ALL, I just need it to sort the data

Upvotes: 0

Views: 58

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522751

You may try adding two sort columns explicitly to your current union query:

SELECT Name, Qty, Amt, Exp
FROM
(
    SELECT Name, Qty, Amt, 'EXP' AS Exp, 1 AS p1, 1 AS p2 FROM T1  -- place this row 1st
    UNION ALL
    SELECT Name, Qty, Amt, Exp, 2, SRNO FROM T2                    -- middle
    UNION ALL
    SELECT Name, Qty, NULL, 'EXP', 3, 1 FROM T3                    -- place this row 3rd
) t
ORDER BY
    p1, p2;

Here we add two dummy sorting levels to the union query. The p1 sorting level puts the single record from T1 first, the single record from T3 last, and the records from T2 in the middle. Then, the second sorting level p2 sorts the T2 records ascending by the SRNO. If SRNO is not an integer column, then we would have to make slight changes to the above answer, but it should still work.

Upvotes: 2

Related Questions