SQL SERVER – UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL

I often see developers trying following syntax while using ORDER BY.

SELECT Columns
FROM TABLE1
ORDER BY Columns
UNION ALL
SELECT Columns
FROM TABLE2
ORDER BY Columns

However the above query will return following error.

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘ORDER’.

It is not possible to use two different ORDER BY in the UNION statement. UNION returns single resultsetand as per the Logical Query Processing Phases.

However, if your requirement is such that you want your top and bottom query of the UNION resultset independently sorted but in the same resultset you can add an additional static column and order by that column. Let us re-create the same scenario.

First create two tables and populated with sample data.

USE tempdb
GO
-- Create table
CREATE TABLE t1 (ID INT, Col1 VARCHAR(100));
CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));
GO
-- Sample Data Build
INSERT INTO t1 (ID, Col1)
SELECT 1, 'Col1-t1'
UNION ALL
SELECT 2, 'Col2-t1'
UNION ALL
SELECT 3, 'Col3-t1';
INSERT INTO t2 (ID, Col1)
SELECT 3, 'Col1-t2'
UNION ALL
SELECT 2, 'Col2-t2'
UNION ALL
SELECT 1, 'Col3-t2';
GO

Solarwinds

If we SELECT the data from both the table using UNION ALL .

-- SELECT without ORDER BY
SELECT ID, Col1
FROM t1
UNION ALL
SELECT ID, Col1
FROM t2
GO

We will get the data in following order.

SQL SERVER - UNION ALL and ORDER BY - How to Order Table Separately While Using UNION ALL unionorderby1

However, our requirement is to get data in following order.

SQL SERVER - UNION ALL and ORDER BY - How to Order Table Separately While Using UNION ALL unionorderby3

If we need data ordered by Column1 we can ORDER the resultset ordered by Column1.

-- SELECT with ORDER BY
SELECT ID, Col1
FROM t1
UNION ALL
SELECT ID, Col1
FROM t2
ORDER BY ID
GO

SQL SERVER - UNION ALL and ORDER BY - How to Order Table Separately While Using UNION ALL unionorderby2

Now to get the data in independently sorted in UNION ALL let us add additional column OrderKey and use ORDER BY  on that column. I think the description does not do proper justice let us see the example here.

-- SELECT with ORDER BY - with ORDER KEY
SELECT ID, Col1, 'id1' OrderKey
FROM t1
UNION ALL
SELECT ID, Col1, 'id2' OrderKey
FROM t2
ORDER BY OrderKey, ID
GO

The above query will give the desired result.

SQL SERVER - UNION ALL and ORDER BY - How to Order Table Separately While Using UNION ALL unionorderby3

Now do not forget to clean up the database by running the following script.

-- Clean up
DROP TABLE t1;
DROP TABLE t2;
GO

Here is the complete script used in this example.

USE tempdb
GO
-- Create table
CREATE TABLE t1 (ID INT, Col1 VARCHAR(100));
CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));
GO
-- Sample Data Build
INSERT INTO t1 (ID, Col1)
SELECT 1, 'Col1-t1'
UNION ALL
SELECT 2, 'Col2-t1'
UNION ALL
SELECT 3, 'Col3-t1';
INSERT INTO t2 (ID, Col1)
SELECT 3, 'Col1-t2'
UNION ALL
SELECT 2, 'Col2-t2'
UNION ALL
SELECT 1, 'Col3-t2';
GO
-- SELECT without ORDER BY
SELECT ID, Col1
FROM t1
UNION ALL
SELECT ID, Col1
FROM t2
GO
-- SELECT with ORDER BY
SELECT ID, Col1
FROM t1
UNION ALL
SELECT ID, Col1
FROM t2
ORDER BY ID
GO
-- SELECT with ORDER BY - with ORDER KEY
SELECT ID, Col1, 'id1' OrderKey
FROM t1
UNION ALL
SELECT ID, Col1, 'id2' OrderKey
FROM t2
ORDER BY OrderKey, ID
GO
-- Clean up
DROP TABLE t1;
DROP TABLE t2;
GO

I am sure there are many more ways to achieve this, what method would you use if you have to face the similar situation?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQLAuthority News – Windows Efficiency Tricks and Tips – Personal Technology Tip
Next Post
SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

Related Posts

33 Comments. Leave new

  • I want TO combine TOP 5 RESULT SET OF two tables AND based ON totalBook

    SELECT TOP 5 student, TotalBook
    FROM school_one
    ORDER BY Totalbook
    UNION ALL
    SELECT TOP 5 student, TotalBook
    FROM school_two
    ORDER BY Totalbook

    This IS NOT working

    Reply
    • Hi Suraz. It is not possible to use 2 separate Order By in Union. You can add an additional static column and order by that column.

      You want to use the concept “SELECT with ORDER BY – with ORDER KEY”

      Select Student, TotalBook, ‘tb1’ OrderKey
      from school_one
      Union All
      Select Student, TotalBook, ‘tb2’ OrderKey
      from school_two
      order by OrderKey, TotalBook

      Reply
  • With this script you can use custom ordering in each sql clause:

    USE tempdb
    GO
    — Create table
    CREATE TABLE t1 (ID INT, Col1 VARCHAR(100));
    CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));
    GO
    — Sample Data Build
    INSERT INTO t1 (ID, Col1)
    SELECT 1, ‘Col1-t1’
    UNION ALL
    SELECT 2, ‘Col2-t1’
    UNION ALL
    SELECT 3, ‘Col3-t1’;
    INSERT INTO t2 (ID, Col1)
    SELECT 3, ‘Col1-t2’
    UNION ALL
    SELECT 2, ‘Col2-t2’
    UNION ALL
    SELECT 1, ‘Col3-t2′;
    GO

    — SELECT without ORDER BY

    SELECT ID, Col1
    FROM t1
    UNION ALL
    SELECT ID, Col1
    FROM t2
    GO

    — SELECT with ORDER BY

    SELECT ID, Col1
    FROM t1
    UNION ALL
    SELECT ID, Col1
    FROM t2
    ORDER BY ID
    GO

    — SELECT with ORDER BY – with ORDER KEY

    WITH
    T11 AS
    (
    SELECT TOP (SELECT count(*) FROM t1) ID, Col1,’id1’ as OrderKey
    FROM t1
    order by ID –you can use ‘desc’ or add more columns to order
    ),

    T22 AS
    (
    SELECT TOP (SELECT count(*) FROM t2) ID, Col1, ‘id2’ as OrderKey
    FROM t2
    order by ID –you can use ‘desc’ or add more columns to order
    )

    select * from T11
    union all
    select * from T22 order by OrderKey
    GO

    — Clean up
    DROP TABLE t1;
    DROP TABLE t2;
    GO

    Reply

Leave a Reply

Menu