TSQL Beginners Challenge 1 - Find the second highest salary for each department - Solution to TSQL Beginners Challenge 1

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


	TSQL Beginners Challenges

Upload Image

Close it

Select File

The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.


	Like

Invite Friends

TSQL Beginners Challenge 1 - Find the second highest salary for each department

Home · Challenge Details · Comments · Solutions · Fans (186) · Preferences

Solution to TSQL Beginners Challenge 1

Jul 7 2011 7:17AM by Pothiraj

Solution to TSQL Beginners Challenge 1

;With CTE (EmployeeID, SalaryRank)
AS
(
SELECT EmployeeID,RANK() Over (partition by Department ORder by salary Desc) AS SalaryRank
From @Employees 
)
Select E.EmployeeID, E.EmployeeName, E.Department, E.Salary
From CTE C
Inner Join @Employees E
On C.EmployeeID = E.EmployeeID 
WHERE C.SalaryRank = 2
Order by E.Salary

Tags:

Pothiraj

26 · 6% · 1962

Liked


Post this to my facebook wall
Tweet this
Like this


	Submit

Your Comment

Sign Up or Login to post a comment.

TSQL Beginners Challenge 1 - Find the second highest salary for each department

Solution to TSQL Beginners Challenge 1

Your Comment