October 30, 2012 by pinaldave
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’.
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 tableCREATE TABLE t1 (ID INT, Col1 VARCHAR(100));CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));GO-- Sample Data BuildINSERT INTO t1 (ID, Col1)SELECT 1, 'Col1-t1'UNION ALLSELECT 2, 'Col2-t1'UNION ALLSELECT 3, 'Col3-t1';INSERT INTO t2 (ID, Col1)SELECT 3, 'Col1-t2'UNION ALLSELECT 2, 'Col2-t2'UNION ALLSELECT 1, 'Col3-t2';GO
If we SELECT the data from both the table using UNION ALL .
-- SELECT without ORDER BYSELECT ID, Col1FROM t1UNION ALLSELECT ID, Col1FROM t2
GO
We will get the data in following order.
However, our requirement is to get data in following order.
If we need data ordered by Column1 we can ORDER the resultset ordered by Column1.
-- SELECT with ORDER BYSELECT ID, Col1FROM t1UNION ALLSELECT ID, Col1FROM t2ORDER BY ID
GO
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 KEYSELECT ID, Col1, 'id1' OrderKeyFROM t1UNION ALLSELECT ID, Col1, 'id2' OrderKeyFROM t2ORDER BY OrderKey, ID
GO
The above query will give the desired result.
Now do not forget to clean up the database by running the following script.
-- Clean upDROP TABLE t1;DROP TABLE t2;GO
Here is the complete script used in this example.
USE tempdb
GO-- Create tableCREATE TABLE t1 (ID INT, Col1 VARCHAR(100));CREATE TABLE t2 (ID INT, Col1 VARCHAR(100));GO-- Sample Data BuildINSERT INTO t1 (ID, Col1)SELECT 1, 'Col1-t1'UNION ALLSELECT 2, 'Col2-t1'UNION ALLSELECT 3, 'Col3-t1';INSERT INTO t2 (ID, Col1)SELECT 3, 'Col1-t2'UNION ALLSELECT 2, 'Col2-t2'UNION ALLSELECT 1, 'Col3-t2';GO-- SELECT without ORDER BYSELECT ID, Col1FROM t1UNION ALLSELECT ID, Col1FROM t2
GO-- SELECT with ORDER BYSELECT ID, Col1FROM t1UNION ALLSELECT ID, Col1FROM t2ORDER BY ID
GO-- SELECT with ORDER BY - with ORDER KEYSELECT ID, Col1, 'id1' OrderKeyFROM t1UNION ALLSELECT ID, Col1, 'id2' OrderKeyFROM t2ORDER BY OrderKey, ID
GO-- Clean upDROP 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 (http://blog.sqlauthority.com)
Let us visualize a scenario. An application has lots of different operations and many of them are very sensitive operations. The common practice was to do give application specific role which has more permissions and access level. When a regular user login (not system admin), he/she might have very restrictive permissions. The application itself had a user name and password which means applications can directly login into the database and perform the operation. Developers were well aware of the username and password as it was embedded in the application. When developer leaves the organization or when the password was changed, the part of the application had to be changed where the same username and passwords were used. Additionally, developers were able to use the same username and password and login directly to the same application.
