I have a table: `[tblEmp]`

```
EmpId | EmpName | DeptId
```

and a table `[tblSalary]`

```
EmpId | Salary
```

and I need to find the list of employees who have max salary in their department.

I could achieve this by:

```
SELECT *
FROM tblEmp
JOIN tblSal ON tblSal.EmpId = tblEmp.EmpId
WHERE LTRIM(STR(deptid)) + LTRIM(STR(salary)) IN (
SELECT LTRIM(STR(deptid)) + LTRIM(STR(MAX(salary)))
FROM tblSal
JOIN tblEmp ON tblSal.EmpId = tblEmp.EmpId
GROUP BY DeptId
)
```

Is there a better way to achieve the list ?

You could try using ROW_NUMBER.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Something like

```
;WITH Employees AS (
SELECT e.*,
ROW_NUMBER() OVER(PARTITION BY e.DeptId ORDER BY s.salary DESC) RowID
FROM [tblEmp] e INNER JOIN
[tblSalary] s ON e.EmpId = s.EmpId
)
SELECT *
FROM Employees
WHERE RowID = 1
```

This will however not return Employees that have the same salry in the same department.

For that you might want to look at RANK (Transact-SQL) or DENSE_RANK (Transact-SQL)instead of ROW_NUMBER.

Rank: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.If two or more rows tie for a rank, each tied rows receives the same rank.

Dense_Rank: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.