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.
Parv The IT Geek 