TSQL Beginners Challenges - TSQL Beginners Challenge 1 - Find the second highest salary for each department (Solutions)

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

Liked


	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

Submit a Solution

1
Liked

dense_rank, set of six records

dense_rank...

ThatGuyDuncan Posted on Dec 26 2011 1:31PM
Profile

1
Liked

2
Comments

Find the second highest salary

first of all find highest salary then get one less than higesht salary ...

vikramsehgal Posted on Dec 22 2011 11:42PM
Profile

1
Liked

We can use Dense_Rank and Rank to acheive the same results.

Nice Challenge to refresh the memory :)...

vanne040 Posted on Dec 22 2011 8:52AM
Profile

2
Liked

2
Comments

Solution

with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname) select * from CTE where row=2 ...

Ravi Tuvar Posted on Dec 16 2011 10:06PM
Profile

1
Liked

Find the second highest salary for each department

;with CTE_SAL(EmployeeID, EmployeeName, Department, Salary,ROW_ID) AS(select top 2 EmployeeID, EmployeeName, Department, Salary ,ROW_NUMBER() OVER(ORDER BY SALARY DESC) from employees) select EmployeeID, EmployeeName, Department, Salary from CTE_SAL whe...

Satheesh Variath Posted on Dec 14 2011 4:45AM
Profile · Facebook

1
Liked

2
Comments

Find the second highest salary for each department

We need to use RANK function for the Ordering purpose. We want order for each department so also need to use PARTITION BY clause. I have used CTE to get the required information and filter the result set so we can get second highest for each department....