Find 2 highest salary from each depart ?

Anjali Khan

8y803

Hi frnds

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar8y ago
Accepted answer
SELECT *
FROM User
WHERE
(
sal IN
(
SELECT TOP (2) sal
FROM User
where User.Experience=5
GROUP BY Department
ORDER BY sal DESC
)
)
+4
Jayesh Vaghasiya8y ago
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
0
Anjali Khan8y ago
thanks pravat and jenith.
+1
Pravat Kumar8y ago
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
+2