Find top Nth employee from each department in terms of salary?

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Find top Nth employee from each department in terms of
salary?

Question Posted / akasha ramanna

13 Answers
21967 Views
RealPage, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Find top Nth employee from each department in terms of salary?..

Answer / saurav kumar

select min(salary) from tablename where salary in (select
top N salary from tablename orderby salary desc)

Is This Answer Correct ?

6 Yes

2 No

Find top Nth employee from each department in terms of salary?..

Answer / ricky

select min(salary) from tablename where salary is(select
top n salary from tablename order by salary desc)

Is This Answer Correct ?

3 Yes

0 No

Find top Nth employee from each department in terms of salary?..

Answer / pradeep nambiar

select distinct(a.salary) from employee a where &n=(select
count(distinct(b.salary)) from employee b where
a.salary=b.salary)

Is This Answer Correct ?

3 Yes

1 No

Find top Nth employee from each department in terms of salary?..

Answer / akhil saxena

select e1.sal from emp e1 where
n-1 = (select count(distinct(e2.sal)) from emp e2
where e2.sal<e1.sal)

Is This Answer Correct ?

3 Yes

2 No

Find top Nth employee from each department in terms of salary?..

Answer / chandra sekhar

CREATE A EMPLOYEES TABLE WITH THE COLUMNS
EMPLOYEEID,EMPLOYEENAME,DEPARTMENT,SALARY
AND INSERT THE RECORDS INTO THE TABLE

with cte
as (
select dense_RANK() over(partition by department order by salary desc) as Rank,
employeeID,employeename,department,salary from
Employees)
select x.employeeID,
x.employeename,
x.department,
x.salary,
x.rank
from cte x
where x.Rank=2
order by x.Salary

NOTE:here i am finding 2nd highest salary from the each department

Is This Answer Correct ?

1 Yes

0 No

Find top Nth employee from each department in terms of salary?..

Answer / madhu sudhan g

Hi,
For suppose the table is like emp having some departments
Here i am retrieving some 'X' department employees

;WITH myTableWithRows AS (
SELECT (ROW_NUMBER() OVER (ORDER BY Salary)) as row,*
FROM emp)
SELECT empname
FROM myTableWithRows
WHERE row = Nth row and
Dept='x'
ORDER BY Salary

Is This Answer Correct ?

0 Yes

0 No

Find top Nth employee from each department in terms of salary?..

Answer / rakhi

Select Salary From [TableNAme] WHERE Salary IN(Select Top
Nth Salary From [TableName] ORDER BY Salary DESC) ORDER BY
DESC

Is This Answer Correct ?

0 Yes

1 No

Find top Nth employee from each department in terms of salary?..

Answer / sandeep modapathi

SELECT DISTINCT MIN(SALARY),DEPARTMENT
FROM (SELECT TOP ((SELECT COUNT(DISTINCT DEPARTMENT) FROM
EMPLOYEE)*@N)
SALARY,DEPARTMENT
FROM EMPLOYEE ORDER BY SALARY DESC
) AS CUR1 GROUP BY DEPARTMENT

try it out ..................
it will show you the Nth top salary for each department

Is This Answer Correct ?

0 Yes

1 No

Find top Nth employee from each department in terms of salary?..

Answer / gopi muluka

Execute below query against Adventureworks database

WITH CTE AS(
SELECT D.Name AS Department,E.EmployeeID,(RANK() OVER
(PARTITION BY D.Name ORDER BY MAX(Rate) DESC)) AS
EmployeeRank, MAX(Rate) AS HourlyRate
FROM [AdventureWorks].[HumanResources].[Employee] E
INNER JOIN HumanResources.EmployeePayHistory PH
ON E.[EmployeeID]=PH.[EmployeeID]
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.EmployeeID=DH.EmployeeID
AND DH.EndDate IS NULL
INNER JOIN HumanResources.Department D
ON DH.DepartmentID=D.DepartmentID
GROUP BY D.Name,E.EmployeeID
)
SELECT * FROM CTE WHERE EmployeeRank=2
ORDER BY Department,EmployeeRank,EmployeeID

Is This Answer Correct ?

1 Yes

3 No

Find top Nth employee from each department in terms of salary?..

Answer / saber ali

select max(salary) from employee where salary in (select top
4 salary from employee order by salary desc)

Is This Answer Correct ?

1 Yes

3 No

Post New Answer