SSMS SQL - ORDER BY stops working when adding UNION ALL?

Question

I have 3 different queries with a UNION ALL between them. With one of the queries, I'm attemping to use an order by, which works when running that batch on its own, but as soon as I introduce the rest of it with the UNIONs, the ordering stops working, as can be seen in the picture.

The SQL is something like below:

SELECT CCR1.* FROM (
SELECT TOP 100 PERCENT
   C.URN , D.COLUMN2, D.COLUMN3
FROM TABLE1 C
   JOIN TABLE2 D ON C.1=D.2
WHERE BLAH
ORDER BY C.URN
) CCR1

UNION ALL

SELECT CCR2.* FROM (
....

Put ORDER BY after all query union – Stanislav Kundii Nov 06 '17 at 11:34 — Stanislav Kundii, Nov 06 '17 at 11:34

score 3 · Accepted Answer · answered Nov 06 '17 at 11:38

3

SQL result sets represent unordered sets, unless you specify an order by for the outermost select. You can do what you want with such an order by:

SELECT cd.*
FROM ((SELECT C.URN , D.COLUMN2, D.COLUMN3, 1 as which
       FROM TABLE1 C JOIN
            TABLE2 D
            ON C.1 = D.2
       WHERE BLAH
      ) UNION ALL
      (SELECT C.URN, D.COLUMN2, D.COLUMN3, 2 as which
       FROM . . .
       WHERE BLAH
      )
     ) cd
ORDER BY WHICH, URN;

The subqueries include an indicator specifying which subquery the rows come from. The outer SELECT uses this information for the ORDER BY.

answered Nov 06 '17 at 11:38

Gordon Linoff

1,242,037
58
646
786

AFAIK you don't need the outer select. In this sqlfiddle the ordering applies to whole unioned set, not just the final component. http://sqlfiddle.com/#!6/ca74b/1 – MatBailie Nov 06 '17 at 11:42
1

Adding in a sort number column to each batch and ending it all with "order by sort, urn" was an embarrassingly simple solution to this. Thanks. – Smeghead Nov 06 '17 at 12:16

score 2 · Answer 2 · answered Nov 06 '17 at 11:35

2

You should add another "order by" at the end of your query:

Select * fom ( your queries ) order by...

answered Nov 06 '17 at 11:35

Josep Oriol Soler

576
7
16

score 1 · Answer 3 · answered Nov 06 '17 at 11:34

1

You have to apply Order by on result of UNION ALL statements

SELECT * FROM (
    SELECT * FROM T1
    UNION ALL
    SELECT * FROM T2
) Res
ORDER BY col

answered Nov 06 '17 at 11:34

Mayur Patil

303
3
17

score 1 · Answer 4 · answered Nov 06 '17 at 11:35

1

Only an final ORDER BY is honored for result set ordering. Although one can specify ORDER BY with a TOP clause in CTEs, subqueries, views, etc., those may not necessarily be used for result ordering so you need to add ORDER BY to the end of the query.

answered Nov 06 '17 at 11:35

Dan Guzman

43,250
3
46
71

score 1 · Answer 5 · answered Nov 06 '17 at 11:58

As others have mentioned, SQL is "set based" and a "set" has no explicit or implicit ordering. The data only becomes ordered at the point in time that it returned from the database to the client, and only if you provide a specific ORDER BY clause.

In this example SQL makes no guarantees about the order of the data returned to the client...

SELECT * FROM (SELECT x, y FROM z ORDER BY x, y) sub

Even though there is an ORDER BY clause. The existence of the outer query means that the ordering in the inner query is "out-of-scope". It may come in the order you hope, it's just not guaranteed (and in some dialects would give an error for that reason).

Similarly, the ordering of your two queries is obfuscated by their outer queries and the intervening UNION ALL.

It's also worth noting that when UNIONing multiple sets, you can then ORDER the results without needing an outer query...

SELECT x, y FROM z UNION ALL SELECT a, b FROM c ORDER BY x, y

(The ordering applies to the results of the UNION, which is why it refers to field names that don't even exist in the final SELECT.

For readability I prefer this type of layout...

    SELECT x, y FROM z
UNION ALL
    SELECT a, b FROM c
ORDER BY x, y

To get the results you want, you must specify the ORDER BY in the outermost query. That may mean adding an extra field to the sets which you are UNIONing...

    SELECT 1 AS source, x, y FROM z
UNION ALL
    SELECT 2 AS source, a, b FROM c
ORDER BY source, x, y

SSMS SQL - ORDER BY stops working when adding UNION ALL?

5 Answers5