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

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

1
Liked

1
Comments

solution

Just use a CTE to calculate a RANK, partitioned by Department and ordered by Salary. Then select from the CTE where Rank = 2...

Marc Jellinek Posted on Dec 28 2011 11:19AM
Profile · Blog

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....

Hardik Doshi Posted on Dec 8 2011 10:25PM
Profile · Facebook

1
Liked

Find the second highest salary for each department

Using Rank Function....

Nirav Gandhi Posted on Dec 8 2011 10:21PM
Profile · Blog · Facebook · Twitter

1
Liked

TSQL Beginners Challenge 1 - Solution

...

Nimit Goyal Posted on Nov 12 2011 6:14AM
Profile · Facebook

1
Liked

1
Comments

TSQL Beginners Challenge 1

Without Rank...

SylvainTheroux Posted on Nov 7 2011 11:44AM
Profile

Previous 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ... Next