Pages

Saturday 9 March 2013

How to use "UNION" and "ORDER BY" together in SQL?

This is one of tricky question and I love tricky question so I am going to write this post. How if I run below query in sql server:
SELECT TOP 1 * from Employee ORDER BY EmpId ASC
UNION
SELECT TOP 1 * from Employee ORDER BY EmpId DESC
This query result in error due to incorrect position of ORDER BY cause we only use ORDER BY clause to the final result set.
Error Message:













There are many ways to recreate above query without error but the easiest one is following:
SELECT *
FROM (
    SELECT TOP 1 * FROM Employee ORDER BY EmpId ASC) FIRST
UNION
SELECT *
FROM (
    SELECT TOP 1 * FROM Employee ORDER BY EmpId DESC) LAST


No comments:

Post a Comment