PLSQL Challenge 2 - Find the second highest salary for each department - Rank Function-In Sqlserver

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


	ORACLE - PLSQL Challenges

Upload Image

Close it

Select File

This challenge is about finding second highest salary for each department


	Like

Invite Friends

PLSQL Challenge 2 - Find the second highest salary for each department

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

Rank Function-In Sqlserver

Nov 2 2011 6:10AM by rajeshmohanrangan

My query gives your expected result


  WITH RankedData AS
(
	SELECT
		SD.EmployeeID,
		SD.EmployeeName,
		SD.Department,
		SD.Salary,
    	Rank() OVER 
			(PARTITION BY SD.Department 
				ORDER BY SD.Salary DESC) AS SalaryRank
    FROM PLC2_EMPLOYEES SD
) 
select  EmployeeID,EmployeeName,Department,Salary from RankedData where SalaryRank =2

Tags:

rajeshmohanrangan

958 · 0% · 27

Liked


Post this to my facebook wall
Tweet this
Like this


	Submit

1 Comments

WITH RankedData AS ( SELECT SD.EmployeeID, SD.EmployeeName, SD.Department, SD.Salary, DenseRank() OVER (PARTITION BY SD.Department ORDER BY SD.Salary DESC) AS SalaryRank FROM PLC2EMPLOYEES SD ) select EmployeeID,EmployeeName,Department,Salary from RankedData where SalaryRank =2

This one also work fine

commented on Nov 2 2011 6:25AM

rajeshmohanrangan

958 · 0% · 27

Your Comment

Sign Up or Login to post a comment.

"Rank Function-In Sqlserver" rated 5 out of 5 by 1 readers