SQL Query - Using Order By in UNION

A

16

87

How can one programmatically sort a union query when pulling data from two tables? For example,

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1

Throws an exception

Note: this is being attempted on MS Access Jet database engine

Aesthete answered 17/10, 2008 at 21:5 Comment(0)

S

121

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION.

In this case

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

Supercilious answered 3/8, 2010 at 7:35 Comment(9)

worked for me when the order by affects the result set (as when using Top x) – Goddaughter 11/5, 2011 at 14:56

Works great for me...you need to make sure that the outer select has a table alias. That bit me. – Amberly 22/4, 2012 at 7:56

I had no problems using this syntax with Microsoft SQL Server Standard (64-bit) version 11.0.5058.0. – Credits 6/1, 2015 at 13:20

@Troy: Not sure if we really need a dummy alias. Worked fine for me without alias on Oracle 12g. – Flautist 16/1, 2015 at 14:45

Maybe I'm missing something but I think the first UNION needs the list of columns – Langland 15/9, 2015 at 8:44

In SSMS you will need to change the sub-queries to SELECT TOP 100 PERCENT in order to use ORDER BY in a sub-query – Point 22/9, 2015 at 17:8

IMPORTANT: in 2014 works with UNION ALL and does not with UNION – Versicolor 15/12, 2015 at 13:5

If I don't provide the alias it doesn't work, if i provide the alias its not taking it. I also used 'AS' term for providing alias. – Saez 17/2, 2016 at 13:41

This is wrong. Order by for union goes after the 2nd select; order by not at outermost level without top/limit is not specified to have any effect. – Lajuanalake 2/8, 2020 at 1:59

A

65

SELECT field1 FROM table1
UNION
SELECT field1 FROM table2
ORDER BY field1

Archive answered 17/10, 2008 at 21:10 Comment(5)

This technically doesn't accomplish what you were logically asking in the original question. – Idonah 10/9, 2010 at 14:49

@Ian Boyd: I take your point but what they are asking makes no logical sense: union operates on sets and sets have no order! – Pneumonoultramicroscopicsilicovolcanoconiosis 15/9, 2011 at 10:18

@Pneumonoultramicroscopicsilicovolcanoconiosis The original author wants to concatenate two ordered sets of results. UNION does not allow that to happen. There may be another construct to do it. There may not. Either way this answer doesn't technically accomplish what author was asking. – Idonah 16/9, 2011 at 13:36

@Ian Boyd: In SQL, ORDER BY is part of a cursor, whereas UNION operates on tables, therefore their code cannot work. I cannot see how you can infer the OP's intention from absurd code. Consider that SQL's UNION removes duplicates: if these are your "ordered sets of results" {1, 2, 3} UNION {2, 4, 6} would the result be {1, 2, 3, 4, 6} or {1, 3, 2, 4, 6}? We don't know because union of "ordered sets of results" is undefined as regards SQL and the OP hasn't specified. – Pneumonoultramicroscopicsilicovolcanoconiosis 16/9, 2011 at 14:22

I am using MYSQL, I have included the field (Ordering field) in all the select statements. Then just added Order by by at the end, Works fine for me. – Recognizor 26/4, 2014 at 13:10

E

58

I think this does a good job of explaining.

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set.

In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

Taken from here: http://www.techonthenet.com/sql/union.php

Earley answered 17/10, 2008 at 21:18 Comment(0)

I

30

Using a concrete example:

SELECT name FROM Folders ORDER BY name
UNION
SELECT name FROM Files ORDER BY name

Files:

name
=============================
RTS.exe
thiny1.etl
thing2.elt
f.txt
tcpdump_trial_license (1).zip

Folders:

name
============================
Contacts
Desktop
Downloads
Links
Favorites
My Documents

Desired Output: (results of first select first, i.e. folders first)

Contacts
Desktop
Downloads
Favorites
Links
My Documents
f.txt
RTMS.exe
tcpdump_trial_license (1).zip
thiny1.etl
thing2.elt

SQL to achieve the desired results:

SELECT name 
FROM (
    SELECT 1 AS rank, name FROM Folders
    UNION 
    SELECT 2 AS rank, name FROM Files) dt
ORDER BY rank, name

Idonah answered 16/9, 2011 at 13:45 Comment(3)

This is the best answer by far – Complacent 28/5, 2015 at 13:20

This is GREAT answer! – Georgie 2/1, 2016 at 15:37

Note - you must give the derived table an alias (as shown in this example with dt) or it won't work. I was puzzled by this for a little while since I'd omitted that detail to begin with and the error message thrown by SSMS is not particularly helpful. – Lather 12/1, 2016 at 16:39

A

17

Here's an example from Northwind 2007:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;

The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.

Acetylcholine answered 17/10, 2008 at 21:16 Comment(0)

B

9

(SELECT table1.field1 FROM table1 
UNION
SELECT table2.field1 FROM table2) ORDER BY field1

Work? Remember think sets. Get the set you want using a union and then perform your operations on it.

Blowzed answered 17/10, 2008 at 21:11 Comment(1)

You can also use ordinal values in your order by clause in case the fields you wish to sort on are named differently – Earley 17/10, 2008 at 21:21

I

5

SELECT table1Column1 as col1,table1Column2 as col2
    FROM table1
UNION
(    SELECT table2Column1 as col1, table1Column2 as col2
         FROM table2
)
ORDER BY col1 ASC

Intercontinental answered 17/10, 2008 at 21:31 Comment(0)

R

4

SELECT field1
FROM ( SELECT field1 FROM table1
       UNION
       SELECT field1 FROM table2
     ) AS TBL
ORDER BY TBL.field1

(use ALIAS)

Rhonarhonchus answered 12/6, 2011 at 3:10 Comment(2)

@DisplacedGuy if MJ has a better answer to a question then then any of the above, and in this case the accepted answer clearly has problems, then MJ should be able to and I encourage him to leave new answers – Curie 20/11, 2013 at 21:14

And btw, MJ's answer is best! (for me at least) – Curie 20/11, 2013 at 21:17

U

4

This is the stupidest thing I've ever seen, but it works, and you can't argue with results.

SELECT *
FROM (
    SELECT table1.field1 FROM table1 ORDER BY table1.field1
    UNION
    SELECT table2.field1 FROM table2 ORDER BY table2.field1
) derivedTable

The interior of the derived table will not execute on its own, but as a derived table works perfectly fine. I've tried this on SS 2000, SS 2005, SS 2008 R2, and all three work.

Unsnap answered 9/11, 2011 at 21:52 Comment(0)

E

2

This is how it is done

select * from 
    (select top 100 percent pointx, pointy from point
     where pointtype = 1
     order by pointy) A
union all
select * from 
    (select top 100 percent pointx, pointy from point
     where pointtype = 2
     order by pointy desc) B

Episiotomy answered 8/10, 2011 at 22:7 Comment(0)

A

2

Browsing this comment section I came accross two different patterns answering the question. Sadly for SQL 2012, the second pattern doesn't work, so here's my "work around"

Order By on a Common Column

This is the easiest case you can encounter. Like many user pointed out, all you really need to do is add an Order By at the end of the query

SELECT a FROM table1
UNION
SELECT a FROM table2
ORDER BY field1

or

SELECT a FROM table1 ORDER BY field1
UNION
SELECT a FROM table2 ORDER BY field1

Order By on Different Columns

Here's where it actually gets tricky. Using SQL 2012, I tried the top post and it doesn't work.

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

Following the recommandation in the comment I tried this

SELECT * FROM 
(
  SELECT TOP 100 PERCENT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT TOP 100 PERCENT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

This code did compile but the DUMMY_ALIAS1 and DUMMY_ALIAS2 override the Order By established in the Select statement which makes this unusable.

The only solution that I could think of, that worked for me was not using a union and instead making the queries run individually and then dealing with them. So basically, not using a Union when you want to Order By

Antic answered 22/10, 2015 at 20:55 Comment(0)

A

1

By using order separately each subset gets order, but not the whole set, which is what you would want uniting two tables.

You should use something like this to have one ordered set:

SELECT TOP (100) PERCENT field1, field2, field3, field4, field5 FROM 
(SELECT table1.field1, table1.field2, table1.field3, table1.field4, table1.field5 FROM table1
UNION ALL 
SELECT table2.field1, table2.field2, table2.field3, table2.field4, table2.field5 FROM  table2) 
AS unitedTables ORDER BY field5 DESC

Apelles answered 24/1, 2012 at 17:7 Comment(0)

A

0

The second table cannot include the table name in the ORDER BY clause.

So...

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

Aesthete answered 17/10, 2008 at 21:7 Comment(2)

What a good question this was. Can you tell whether your version, or the nested one, return the desired results? Or, do they both return the same results? If so, would the (other guy's) nested solution be more performant because it only does ORDER BY once? – Miyasawa 17/10, 2008 at 21:15

I'm not sure the performance benefit on the Jet engine, but I would say the readability is increased due to the nesting. – Aesthete 17/10, 2008 at 21:53

P

0

If necessary to keep the inner sorting:

SELECT 1 as type, field1 FROM table1 
UNION 
SELECT 2 as type, field1 FROM table2 
ORDER BY type, field1

Patrinapatriot answered 13/3, 2015 at 15:1 Comment(0)

W

0

(SELECT FIELD1 AS NEWFIELD FROM TABLE1 ORDER BY FIELD1)
UNION
(SELECT FIELD2 FROM TABLE2 ORDER BY FIELD2)
UNION
(SELECT FIELD3 FROM TABLE3 ORDER BY FIELD3) ORDER BY NEWFIELD

Try this. It worked for me.

Winstonwinstonn answered 14/7, 2015 at 18:20 Comment(0)

H

0

For Sql Server 2014/2012/Others(Not Checked) :

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) 
as DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) 
as DUMMY_ALIAS2

Historian answered 23/5, 2016 at 9:7 Comment(1)

You get a compile error in 2012 trying this one. The script will not work for a stored procedure. You need the top clause. – Scratches 14/9, 2016 at 15:56

Order By on a Common Column

Order By on Different Columns

Recommended topics

Hot tags