TSQL Challenge on BeyondRelational.COM — Parv The IT Geek

I was asked by one of my junior colleagues to help him with a puzzle he had seen on BeyondRelational.COM which I thought would be a nice challenge. I liked the premise of the scenario and I always liked a challenge decided to show my colleague how I would approach and resolve this problem.

I was a little annoyed that the example code to create the test data didn’t work so did a quick fix which I have posted below.

CREATE TABLE Firstchallenge(

EmployeeID INT IDENTITY,

EmployeeName VARCHAR(15),

Department VARCHAR(15),

Salary NUMERIC(16,2)

)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('T Cook','Finance', 40000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Michael','Finance', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('A Smith','Finance', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Adams','Finance', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('M Williams','IT', 80000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('D Jones','IT', 40000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('J Miller','IT', 50000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('L Lewis','IT', 50000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('A Anderson','Back-Office', 25000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('S Martin','Back-Office', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('J Garcia','Back-Office', 15000)

INSERT INTO Firstchallenge(EmployeeName, Department, Salary)

VALUES('T Clerk','Back-Office', 10000)

I told him that it would be quite simple to get the Ranking by using the RANK command and Partitioning the data by Department. However, I know you can’t filter by rankings from past experience but I knew if I put it into a subquery I could then filter the data which I have done below.

SELECT [EmployeeID]

,[EmployeeName]

,[Department]

,[Salary]

FROM

(

SELECT [EmployeeID]

,[EmployeeName]

,[Department]

,[Salary]

,RANK() OVER (PARTITION BY [Department]ORDER BY [Salary] DESC) AS 'Salary Rank'

FROM [TESTDB].[dbo].[Firstchallenge]

) A

WHERE [Salary Rank] = 2

I know that I could improve the query by using a CTE but I was happy that I was able to to get the result required and will probably put the CTE query in a future update of this article.

Originally published at https://parvtheitgeek.com on January 19, 2015.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: