CODEWITHSUNDEEP
sqlsql-order-byunion
markus
markus

Reputation: 40685

How can I order entries in a UNION without ORDER BY?

How can I be sure that my result set will have a first and b second? It would help me to solve a tricky ordering problem.

Here is a simplified example of what I'm doing:

SELECT a FROM A LIMIT 1 
UNION 
SELECT b FROM B LIMIT 1;

Upvotes: 21

Views: 16689

Answers (7)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

SELECT col
FROM 
   (
       SELECT a col, 0 ordinal FROM A LIMIT 1
       UNION ALL
       SELECT b, 1 FROM B LIMIT 1
   ) t
ORDER BY ordinal

Upvotes: 35

Quassnoi
Quassnoi

Reputation: 425823

Your result set with UNION will eliminate distinct values.

I can't find any proof in documentation, but from 10 years experience I can tell that UNION ALL does preserve order, at least in Oracle.

Do not rely on this, however, if you're building a nuclear plant or something like that.

Upvotes: 5

Dana
Dana

Reputation: 32997

I don't think order is guaranteed, at least not across all DBMS.

What I've done in the past to control the ordering in UNIONs is:

(SELECT a, 0 AS Foo FROM A LIMIT 1)
UNION
(SELECT b, 1 AS Foo FROM B LIMIT 1)
ORDER BY Foo

Upvotes: 21

Bill
Bill

Reputation: 68

The short answer is yes, you will get A then B.

Upvotes: -6

cagcowboy
cagcowboy

Reputation: 30888

I would have thought not, since the database would most likely need to do an ORDER BY in order to the UNION.

UNION ALL might behave differently, but YMMV.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132710

No, the order of results in a SQL query is controlled only by the ORDER BY clause. It may be that you happen to see ordered results without an ORDER BY clause in some situation, but that is by chance (e.g. a side-effect of the optimiser's current query plan) and not guaranteed.

What is the tricky ordering problem?

Upvotes: 2

Thomas Jones-Low
Thomas Jones-Low

Reputation: 7161

I know for Oracle there is no way to guarantee which will come out first without an order by. The problem is if you try it it may come out in the correct order even for most of the times you run it. But as soon as you rely on it in production, it will come out wrong.

Upvotes: 1

Related Questions