1st and 2 nd Highest Salary Dept Wise.

CREATE TABLE EmpSalWithDeptWise
(EmpID INT,EName CHAR(5),Salary MONEY,DeptID INT)
GO
empid ename sal deptid
101 A 23000 10
102 B 12000 10
103 C 8000 20
104 D 15000 30
105 E 32000 10
106 F 22000 20
107 G 5000 30
108 H 14000 30
109 I 16000 20
110 J 19000 10
111 K 7000 10
112 L 45000 20
113 M 22000 10
114 N 16000 10
115 O 11000 20
GO
SELECT Q.empid,Q.ename, Q.Salary,Q.deptid ,Q.Salary
FROM (
SELECT T.empid, T.ename, deptid ,Salary,
Row_Order =(
SELECT COUNT(T1.empid) + 1
FROM dbo.EmpSalWithDeptWise T1
WHERE T1.Salary < T.Salary
AND t1.deptid = t.deptid
)

FROM dbo.EmpSalWithDeptWise T)Q
WHERE Q.Row_Order <= 2
ORDER BY Q.deptid