When working with SQL Server, it is common to use the UNION statement to combine the results of multiple SELECT queries into a single result set. However, there is a common mistake that developers make when using the ORDER BY clause with UNION.

Consider the following example:

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

This query will result in an error: “Incorrect syntax near the keyword ‘ORDER'”. This is because it is not possible to use two different ORDER BY clauses in a UNION statement.

However, if your requirement is such that you want the top and bottom queries of the UNION result set to be independently sorted but in the same result set, you can add an additional static column and order by that column.

Let’s recreate the scenario with sample data:

CREATE TABLE t1 (ID INT, Col1 VARCHAR(100));
CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));

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';

If we select the data from both tables using UNION ALL without an ORDER BY clause, we will get the data in the following order:

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

However, if our requirement is to get the data ordered by Column1, we can use the ORDER BY clause on the result set:

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

Now, to get the data independently sorted in the UNION ALL result set, we can add an additional column called OrderKey and use ORDER BY on that column:

SELECT ID, Col1, 'id1' OrderKey FROM t1
UNION ALL
SELECT ID, Col1, 'id2' OrderKey FROM t2
ORDER BY OrderKey, ID

The above query will give us the desired result:

ID | Col1   | OrderKey
---+--------+---------
1  | Col1-t1| id1
2  | Col2-t1| id1
3  | Col3-t1| id1
1  | Col3-t2| id2
2  | Col2-t2| id2
3  | Col1-t2| id2

Remember to clean up the database by running the following script:

DROP TABLE t1;
DROP TABLE t2;

There are many ways to achieve the desired result, but this method using an additional static column and ORDER BY is a simple and effective approach.

What method would you use if you faced a similar situation? Let us know in the comments!