Order by clause with Union in Sql Server

Problem: use of order by with union statement.

We will discuss different ways of using order by statement with union statement.

mostly developer tries to add order by in both union statements like below:

SELECT COLUMN1,COLUMN2 FROM TABLE1
    ORDER BY COLUMN1
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2
    ORDER BY COLUMN1

This query is not correct and it will throw an error:

incorrect syntax near order by.

SQL union does not support two order by statement as union is used to combine two queries result as one so how is it possible to have two order by statements for one result set.

However, there are other ways to order both query as per requirements but not with two order by statements. Let's have a look first on simple syntax that how we can use order by with union statement.

SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    ORDER BY COLUMN1

This query will order the union result per Column1 value. this is the most simple and cleaner statement we can use for order by with union statement.

Now another scenario if you have some complex query and it’s difficult for you to handle the order by as mentioned in above query then you can use below query it will give you little more control over the query and you can perform some more operation.

SELECT * FROM (
    SELECT COLUMN1,COLUMN2 FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2 FROM TABLE2 
    )R
    ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()

I share above query because I face on issue on simple order by with union statement so I resolved with this query. maybe you require the union result be joined with some other table so it will be easy for you to handle with this query. however, it is not as much cleaner and its using also subquery so may be some performance factor affect your results.

Now if you want to order you both queries should be order by independently from each other than you can use below query. for example, you want first and second query of union should be sorted differently/independently then have a look:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
    ORDER BY orderbyKey

This query will show first record of top query and then bottom query records accordingly.

If we want to sort the result set that it first shows the top query result and then bottom query result and these should be order by Created Date descending then we can do this below:

SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
    UNION ALL
    SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
    ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 

Complete Script:

USE tempdb
GO
-- Create table
CREATE TABLE TABLE1 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
CREATE TABLE TABLE2 (COLUMN1 INT, COLUMN2 VARCHAR(50),CREATED_DATE DATETIME);
GO
--INSERT Sample Data 
INSERT INTO TABLE1 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 1, 'VAL1_TABLE1',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE1',GETDATE()+1
UNION ALL
SELECT 3, 'VAL3_TABLE1',GETDATE()+2;
INSERT INTO TABLE2 (COLUMN1, COLUMN2,CREATED_DATE)
SELECT 3, 'VAL1_TABLE2',GETDATE()
UNION ALL
SELECT 2, 'VAL2_TABLE2',GETDATE()+3
UNION ALL
SELECT 1, 'VAL3_TABLE2',GETDATE()+5;
GO

 --1ST
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 ORDER BY COLUMN1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --2ND
 SELECT COLUMN1,COLUMN2 FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 ORDER BY COLUMN1
 go
 --3RD 
 SELECT * FROM
 SELECT COLUMN1,COLUMN2 FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2 FROM TABLE2
 )R
 ORDER BY R.COLUMN1,R.COLUMN2,GETDATE()
 go
 --4TH
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' as orderbyKey FROM TABLE1
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' as orderbyKey FROM TABLE2 
 ORDER BY orderbyKey
 go
 --5TH<
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'1' AS ORDERBYKEY FROM TABLE1 
 UNION ALL
 SELECT COLUMN1,COLUMN2,CREATED_DATE,'2' AS ORDERBYKEY FROM TABLE2 
 ORDER BY ORDERBYKEY ASC,CREATED_DATE DESC 
go
-- Clean up
DROP TABLE TABLE1;
DROP TABLE TABLE2;
GO

Comments and suggestions are always welcome!