Find 2 highest salary from each depart ?

Question

Hi frnds Find 2 highest salary from each depart who have completed 5 years in org.

Jenith Thakkar · Answer

SELECT * FROM User WHERE ( sal IN ( SELECT TOP (2) sal FROM User where User.Experience=5 GROUP BY Department ORDER BY sal DESC ) )

Pravat Kumar · Answer

Hi Anjali, Please execute the below query. CREATE TABLE #tblCompany ( Id INT PRIMARY KEY identity(1, 1) ,Salary INT ,Department VARCHAR(50) ,Experience INT ,CreatedDate DATETIME DEFAULT GETDATE() ) INSERT #tblCompany(Salary,Department,Experience) VALUES(10000,'IT',5) INSERT #tblCompany(Salary,Department,Experience) VALUES(20000,'IT',6) INSERT #tblCompany(Salary,Department,Experience) VALUES(5000,'IT',3) INSERT #tblCompany(Salary,Department,Experience) VALUES(30000,'IT',7) INSERT #tblCompany(Salary,Department,Experience) VALUES(15000,'HR',5) INSERT #tblCompany(Salary,Department,Experience) VALUES(25000,'HR',6) INSERT #tblCompany(Salary,Department,Experience) VALUES(5500,'HR',3) INSERT #tblCompany(Salary,Department,Experience) VALUES(35000,'HR',7) SELECT top 2 Department, Salary FROM #tblCompany t WHERE Experience >= 5 AND Salary SELECT Max(Salary) FROM #tblCompany WHERE Experience >= 5 AND Department =t.Department ) order by Salary desc DROP TABLE #tblCompany

Anjali Khan · Answer

thanks pravat and jenith.

Jayesh Vaghasiya · Answer

CREATE TABLE #tblCompany ( Id INT PRIMARY KEY identity(1, 1) ,Salary INT ,Department VARCHAR(50) ,Experience INT ) INSERT #tblCompany(Salary,Department,Experience) VALUES(10000,'IT',5) INSERT #tblCompany(Salary,Department,Experience) VALUES(20000,'IT',6) INSERT #tblCompany(Salary,Department,Experience) VALUES(50000,'IT',3) INSERT #tblCompany(Salary,Department,Experience) VALUES(30000,'IT',7) INSERT #tblCompany(Salary,Department,Experience) VALUES(15000,'HR',5) INSERT #tblCompany(Salary,Department,Experience) VALUES(25000,'HR',6) INSERT #tblCompany(Salary,Department,Experience) VALUES(55000,'HR',3) INSERT #tblCompany(Salary,Department,Experience) VALUES(35000,'HR',7) INSERT #tblCompany(Salary,Department,Experience) VALUES(37000,'HR',3) SELECT * FROM #tblCompany t WHERE Experience >= 5 AND Salary in (SELECT top 2 Salary FROM #tblCompany WHERE Experience >= 5 AND Department =t.Department order by Department,Salary desc ) order by Salary desc DROP TABLE #tblCompany

Find 2 highest salary from each depart ?

Answers (4)