Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog incorporates articles about ASP.Net 3.5 and 4.0. Also good amount of concentration is laid on .net projects,book reviews,C# articles,jobs in present industry and some general topics.
Browse by Tags · View All
ARTICLE 61
SQL Server 31
TSQL 30
C# 16
ASP.Net 11
JumpStart 8
GENERAL 8
ASP.Net MVC 7
C# 4.0 6
ASP.Net 4.5 5

Archive · View All
December 2012 22
July 2012 12
May 2012 8
January 2013 7
April 2013 4
March 2013 4
February 2013 4
January 2012 4
August 2012 2
May 2013 1

Rami Vemula's Blog

Custom Ordering Resultset using UNION ALL and OVER Clause in TSQL

Dec 29 2012 12:00AM by rami   

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


Republished from Rami Vemula [15 clicks].  Read the original version here [3 clicks].

rami
538 · 0% · 70
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Custom Ordering Resultset using UNION ALL and OVER Clause in TSQL" rated 5 out of 5 by 1 readers
Custom Ordering Resultset using UNION ALL and OVER Clause in TSQL , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]