If Object_Id('tempdb.dbo.#EmployeeMaster') Is Not Null
Begin
Drop Table dbo.#EmployeeMaster
End
Go
Create Table dbo.#EmployeeMaster
(
EmpNo Int,
DeptId Int,
Salary Numeric(18,2)
)
Insert Into dbo.#EmployeeMaster values(1,1,4000)
Insert Into dbo.#EmployeeMaster values(2,2,1000)
Insert Into dbo.#EmployeeMaster values(3,3,400)
Insert Into dbo.#EmployeeMaster values(4,1,100)
Insert Into dbo.#EmployeeMaster values(5,2,5000)
Insert Into dbo.#EmployeeMaster values(6,3,25000)
Insert Into dbo.#EmployeeMaster values(7,1,56222)
Insert Into dbo.#EmployeeMaster values(8,2,5652)
Insert Into dbo.#EmployeeMaster values(9,3,1000)
Select *From dbo.#EmployeeMaster
Select e.EmpNo, e.DeptId, e.Salary
From dbo.#EmployeeMaster e
Where 1>=
(Select COUNT(1) From dbo.#EmployeeMaster e1
Where e.Salary <= e1.Salary And e.DeptId = e1.DeptId )
=============================================
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
EmpNo DeptId Salary
----------- ----------- ---------------------------------------
1 1 4000.00
2 2 1000.00
3 3 400.00
4 1 100.00
5 2 5000.00
6 3 25000.00
7 1 56222.00
8 2 5652.00
9 3 1000.00
(9 row(s) affected)
EmpNo DeptId Salary
----------- ----------- ---------------------------------------
6 3 25000.00
7 1 56222.00
8 2 5652.00
(3 row(s) affected)
