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

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)

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

34 Comments. Leave new

  • It seems like the title is wrong, you are not ordering the tables separately (which to me implies that a different order by clause is used for different tables), you are just “clustering” the rows from the same table together.

    Reply
  • Lakshmi Ganesh
    October 30, 2012 3:02 pm

    Nice post. Please send all new tips for below mail id. Thanks in advance.

    Reply
  • Lakshmi Ganesh
    October 30, 2012 3:03 pm

    Request you send all further notifications. Thanks

    Reply
  • Need to really appreciate it…Nice Post.

    Regards,
    Girijesh

    Reply
  • Sanjay Monpara
    October 30, 2012 7:58 pm

    If you want different order for both query then you can do it like this,

    select * from (SELECT top 100 percent ID, Col1 FROM t1 order by id) a
    UNION all
    select * from (SELECT top 100 percent ID, Col1 FROM t2 order by id desc) b

    ID Col1
    1 Col1-t1
    2 Col2-t1
    3 Col3-t1
    3 Col1-t2
    2 Col2-t2
    1 Col3-t2

    Reply
    • boss could u test the above query???

      Reply
      • Sanjay Monpara
        June 27, 2015 11:20 am

        Hi Ravi,
        Query is ok, you just need to change column name in order by as per required output

        select * from (SELECT top 100 percent ID, Col1 FROM t1 order by col1) a
        UNION all
        select * from (SELECT top 100 percent ID, Col1 FROM t2 order by col1 desc) b

        select * from (SELECT top 100 percent ID, Col1 FROM t1 order by id) a
        UNION all
        select * from (SELECT top 100 percent ID, Col1 FROM t2 order by id) b

        ID Col1
        1 Col1-t1
        2 Col2-t1
        3 Col3-t1
        3 Col1-t2
        2 Col2-t2
        1 Col3-t2

        Main thing is that, you can use order by autonomously using this way.

  • Hi, it might be easier to do the select in a subquery and the union all after?

    Reply
  • In this case, I would use CTE

    because in this last SQL Statement, it’s giving one additional column as an output. If you want to keep the same output, use CTE instead.

    Thanks,

    Reply
  • SELECT * FROM
    (SELECT ID, Col1 FROM t1
    UNION ALL
    SELECT ID, Col1 FROM t2)
    ORDER BY ID

    Reply
    • Whn i am trying to execute the above query getting following errors.

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

      Reply
      • Use alias name

        SELECT * FROM
        (SELECT ID, Col1 FROM t1
        UNION ALL
        SELECT ID, Col1 FROM t2) as t
        ORDER BY ID

  • select * from
    (SELECT id,col1 FROM
    (SELECT ID, Col1 FROM t1
    order by ID) t11

    UNION ALL

    SELECT id,col1 FROM
    (SELECT ID, Col1 FROM t2
    order by Col1) t22)final
    order by id

    if seperate ordering of each sql is required plus overall.If overall is not required then remove final.

    Reply
    • Getting the following
      Msg 1033, Level 15, State 1, Line 2
      The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

      Reply
  • I got a lots of info
    thanks

    Reply
  • Hi Pinal,
    I have one query as follows :
    I have tables as Source (few columns A B C D ) ,Intermediate (one column identity ) and
    destination (few columns B C D with inserted identity from Intermediate )

    Now I want to insert records in Intermediate table with querying against A B C D column with few other collumns from different tables .
    then get that identity and insert into Destitnation table with col B C D and other few columns and identity which is from Intermediate table.

    I am doing it into SSIS package with merge statement like :
    MERGE
    INTO Intermediate table
    USING (select A, B, C, D ,Col1,Col2 FROM Source Inner join with table 1 ) T
    ON 1= 0
    WHEN NOT matched then
    INSERT DEFAULT VALUES
    Output inserted.Identity ,T.A,T.B,T.Col1,T.col2
    ;
    And this output I am using to do some tranformation and then inserting into other table.

    So could you tell tell me that is it good approach or any other process to do it ?
    I am having almost more than 5 milion records.

    Thanks in advance.
    waiting for your reply.

    Thanks,
    Amol

    Reply
  • Thank you, this one had me stumped.

    Reply
  • alter procedure GetArticuloXCBarrasEnCentroComercial @CBarras nvarchar(20)
    as
    Select * From
    (

    (select Articulo, NombreArticulo, CodigoBarrasArt, Cod_Familia , Cod_SubFamilia, ImagenArt
    from factunet.dbo.Articulo Where CodigoBarrasArt = @CBarras)

    union

    (select Articulo, NombreArticulo, CodigoBarrasArt, Cod_Familia , Cod_SubFamilia , ImagenArt
    from factumerida.dbo.Articulo Where CodigoBarrasArt = @CBarras)

    union

    (select Articulo COLLATE Modern_Spanish_CI_AS, NombreArticulo COLLATE Modern_Spanish_CI_AS, CodigoBarrasArt COLLATE Modern_Spanish_CI_AS, Cod_Familia COLLATE Modern_Spanish_CI_AS, Cod_SubFamilia COLLATE Modern_Spanish_CI_AS, ImagenArt COLLATE Modern_Spanish_CI_AS
    from factupol.dbo.Articulo Where CodigoBarrasArt = @CBarras)

    )
    AS T
    ORDER BY T.NombreArticulo DESC

    Reply
  • Thanks, you saved me from using google further more!

    Reply
  • wouldn’t it be much easier to just create an additional column in the select statement and sort by that first?:
    SELECT a.Name, a.Address FROM
    (
    SELECT Name, Address, ‘1’ as Sorter FROM Employees Where
    UNION
    SELECT Name, Address, ‘2’ as Sorter FROM Customers Where
    ) AS a
    ORDER BY a.Sorter, a.Name

    This will allow you to sort your list by name, keeping customers and employees separated.

    Reply
  • kishor karpe
    April 3, 2014 6:39 pm

    its okey but i want out put something like this
    1 -from t1
    1-from t2
    2-from t1
    2-from t2
    3-from t1
    3-from t2

    Reply
  • its a classic post really appreciated

    Reply
  • Thank you for the info saved day…

    Reply
  • I have working with some querys and I am using UNION ALL,

    SELECT
    Select number,
    HotList.Day1,
    HotList.Day2

    FROM TABLE

    UNION ALL

    SELECT
    Select number,
    HotList.Emb1,
    HotList.Emb2

    FROM TABLE

    UNION ALL

    SELECT
    Select number,
    HotList.Value1,
    HotList.Value2

    FROM TABLE
    ORDER BY number

    1. The result is the following:

    72247 276.8000 (1)
    72247 23.0000 (2)
    72247 -2810.1000 (3)
    53040 920701.3000 (3)
    53040 18.0000 (2)
    53040 9.0000 (1)

    2. I need the following result:

    72247 276.8000 (1)
    72247 23.0000 (2)
    72247 -2810.1000 (3)
    53040 9.0000 (1)
    53040 18.0000 (2)
    53040 920701.3000 (3)

    I need your help to get the second result.

    Reply
    • Sanjay Monpara
      July 5, 2015 4:50 pm

      Means number column in descending order & third column in ascending order?

      Reply
    • Try this at the end of the second select statement

      order by substring(Emb2,charindex(‘%(‘,Emb2)+1,1)*1

      Reply
  • very nice post ;-)

    Reply

Leave a Reply

Menu