Custom Ordering Resultset using UNION ALL and OVER Clause in TSQL

Today there was an interesting conversation between me and a young SQL Developer on how we can get a custom order of a resultset in TSQL. We spoke about different combinations starting from Table Variables, Temp Tables till Sub-queries. I am not able to agree with most of the preferred answers and wanted to solve the problem with a CTE, at least to maintain code readability and not so many performance implications involved with this approach.

For the sake of simplicity, I have taken a sample employees table with their locations and salaries. I need to order my resultset so that first I would like to get all Employees from Los Angeles area in the descending order of their salaries and then remaining rows needs to follow this groups with descending order of salaries. Now lets check out the code –

/************************** Create TestDB Database ****************************/ 

IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO

USE [TestDB]
GO


/************************** Create Employee Table ****************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Employees')
BEGIN
    DROP TABLE [dbo].[Employees];
END
GO

CREATE TABLE [dbo].[Employees](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL,
    [Location] [nvarchar](32) NULL,
    [Salary] [bigint] NULL
)
GO


/********************** Enter Employee Roles Details ***********************/

INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('R1','San Jose',50000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('R2','San Jose',40000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('R3','Los Angeles',30000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('R4','Austin',10000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('Y1','Portland',20000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('Y2','Pheonix',55000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('Y3','Los Angeles',45000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('Y4','San Jose',65000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('T1','Tacoma',35000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('T2','San Jose',25000);
INSERT INTO [dbo].[Employees] ([EmpName],[Location],[Salary]) VALUES ('T3','Los Angeles',15000);


/************************* Get Los Angeles Employees ***************************
The first select statement gets all the employees from Los Angeles and sort them
by Salaray. Second select statement will get all the remainning employees and 
sort them by salary. Union all would club both the result sets. We have used the
ROW_NUMBER() with OVER clause to get the sorting done appropriately, otherwise
UNION ALL wouldn't allow merging with ORDER BY clause.
********************************************************************************/
SET STATISTICS TIME ON;

WITH EmpLocations AS
(
    SELECT
            [EmpId],
            [EmpName],
            [Location],
            [Salary],
            ROW_NUMBER() OVER(PARTITION BY [Location] ORDER BY [Salary] DESC) AS RN
    FROM
            [dbo].[Employees]
    WHERE
            [Location] = 'Los Angeles'

    UNION ALL

    SELECT
            [EmpId],
            [EmpName],
            [Location],
            [Salary],
            ROW_NUMBER() OVER(ORDER BY [Salary] DESC) AS RN
    FROM
            [dbo].[Employees]
    WHERE
            [Location] != 'Los Angeles'
)
SELECT
        *
FROM
        [EmpLocations];

SET STATISTICS TIME OFF;
GO


/**************************** Drop TestDB Database ******************************/

USE master
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

 

Output –

CustomOrderingWithUnionAll

You may also like...