CREATE VIEW EmployeeWithSalary (empID, empNAME, empROOM, Salary)
AS
SELECT E.empID, E.empNAME, E.empROOM, D.Salary
FROM tblEMP AS E
INNER JOIN tblDETAIL AS D ON E.empID = D.empID
GO

SELECT empID, empNAME, empROOM, Salary
FROM EmployeeWithSalary AS E1
WHERE Salary = (
    SELECT MAX(E2.Salary)
    FROM EmployeeWithSalary AS E2
    WHERE E1.empROOM = E2.empROOM
  )

Declare @tblEMP Table 
(
empID int, 
empNAME varchar(20),
empROOM varchar(10)
)

Insert @TblEmp 
Select 1,'John', 'C#' Union All 
Select 2,'Peter','ASP' Union All 
Select 3,'Otto','C#' Union All 
Select 4,'Adams','C++' Union All 
Select 5,'Henry','VB' Union All 
Select 6,'Potter','C++'

Declare @TblDetail Table 
(
Empid int , 
Salary money
)

Insert @TblDetail
Select 1,300 Union All
Select 2,350 Union All
Select 3,320 Union All
Select 4,310 Union All
Select 5,320 Union All
Select 6,330




Select E.EmpID,E.EmpName,E.EmpRoom,MaxSalary From 
(Select E.EmpID,E.EmpName,E.EmpRoom,F.Salary From @TblEmp As E Inner Join 
@TblDetail f On f.empid = e.empid  )as E 
Inner Join 
(Select Max(t.Salary) MaxSalary ,t1.EmpRoom From @TblDetail t Inner Join @TblEmp t1 on
t.empid = t1.empid 
Group by t1.EmpRoom) As F
On f.EmpRoom = E.EmpRoom And F.MaxSalary= e.Salary
order by MaxSalary