Find top Nth employee from each department in terms of
salary?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How to link up the text file to a Database table?
How can we write or define DDL statements in Sql server and DML statements?
How to access the deleted record of an event?
How to create Clustered Primary Key to table?
Explain indexed views and partitioned view with their syntax.
What is extent? Types of extents?
What are the differences between lost updates and uncommitted dependencies?
Do you know what is xpath?
How to set a database state to offline in ms sql server?
explain different types of joins? : Sql server database administration
create index a on employee(dno) In this,which index was created?
what is the use of database index(apart from the last searching of records) and what is the use of composite key?