Find the highest paid employee from different department

The solution you need is a combination of all of the above, it only gets more complicated because the salaries need to be summed first (probably not the best database design to be honest).

Unfortunately SQL Window functions (like RANK()) cannot be used in a WHERE clause so we need another sub-query or CTE.

This works:

;WITH rankings AS
(
	SELECT [Name], Dept, TotSalary, RANK() OVER (PARTITION BY Dept ORDER BY TotSalary DESC) as r
	FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary 
			FROM Employee GROUP BY Dept, [Name]) q 
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1

This version first sums the salary by department and name (in case people with the same name are in different departments - there's a hint regarding the table schema by the way).

It then RANKs the salaries because, as @Maciej-Los stated, more than one employee could earn the same amount (although @OriginalGriff's solution has a similar effect due to the WHERE clause).

Finally all of that goes into the CTE (it could have been a sub-query) so that we can pick out only the top ranking total salary.

[EDIT - the comments to Solution 3 made me re-read the question and I'v realised that you don't want the highest salary by department, just the highest salary overall. To get that just remove the partition by Dept

;WITH rankings AS
(
	SELECT [Name], Dept, TotSalary, RANK() OVER (ORDER BY TotSalary DESC) as r
	FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary 
			FROM Employee GROUP BY Dept, [Name]) q 
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1

You should use one of ranking function[^]: RANK()[^] Why? Because there can be more than one employee with the highest salary.

Try this:

SELECT a.[Name], a.[Salary], a.[Dept]
FROM employee a INNER JOIN
(
    SELECT [Name], [Dept], [Salary], RANK() OVER(ORDER BY [Salary] DESC) AS [Rank]
    FROM employee 
) as b ON a.[Salary] = b.[Salary] AND a.[Name] = b.[Name] AND a.[Dept] = b.[Dept]
WHERE b.[Rank] = 1

SELECT TOP(1) name,dept,SUM(salary) AS sal FROM employee GROUP BY name,dept ORDER BY sal DESC

If situation like you have both employee details and department in same table and you have to fine the highest paid employee from each department then, you can try below solutions

EmployeeId	EmployeeName	Department	   Salary
1	         Neeraj	        Dot Net	       45000
2	         Ankit	        Java	       5000
3	         Akshay    	    Java	       6000
4	         Ramesh	        Dot Net	       7600
5	         Vikas	        Java	       4000
7	         Neha	        Php	           8500
8	         Shivika	    Php	           4500
9	         Tarun	        Dot Net	       9500

Then you can solve it by using below solutions

Solution - 1

SELECT t.EmployeeName,t.Department,t.Salary FROM(SELECT MAX(Salary) AS TotalSalary,Department FROM Employee GROUP BY Department) AS TempNew  
Inner Join Employee t ON TempNew.Department=t.Department  
and TempNew.TotalSalary=t.Salary 
ORDER BY t.Department ASC

Solution -2

;WITH EmployeeDetails AS (
SELECT EmployeeName, Department, DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank, Salary
FROM Employee 
)
SELECT EmployeeName, Department, Salary FROM EmployeeDetails WHERE SalaryRank=1

OUTPUT

EmployeeName	Department	Salary
Neeraj	         Dot Net	45000
Akshay	         Java	    6000
Neha	         Php	    8500

This will give the answer you are looking for. Enjoy.

SELECT
    NAME,
    SALARY,
    DEPT
FROM
    (SELECT
        ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUM,
        NAME,
        SALARY,
        DEPT
    FROM
        (SELECT
            NAME,
            SUM(SALARY) AS SALARY,
            DEPT
        FROM
            Employee
        GROUP BY
            NAME,
            DEPT
            )A
    )B
WHERE
    ROWNUM = 1

If you have any same salary for the 2 or more employees The Rank function will skip the rank The best solution is using Dense_Rank() :

Select * from (
SELECT EmployeeName, Department,salary , DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DenseRank
FROM Employee
)as Results

WHERE DenseRank =1 ;

In case you have multiple table for companies, employees, salaries

select a.employee_name, a.company_name, a.salary from
(
select e.employee_name, c.company_name, s.SALARY_AMOUNT salary from EMPLOYEES e
join COMPANY c on e.COMPANY_ID = c.COMPANY_ID
join SALARIES s on e.EMPLOYEE_ID = s.EMPLOYEE_ID
) a
join
(
select c.company_name, max(s.SALARY_AMOUNT) salary from EMPLOYEES e
join COMPANY c on e.COMPANY_ID = c.COMPANY_ID
join SALARIES s on e.EMPLOYEE_ID = s.EMPLOYEE_ID
group by company_name
) b
on a.company_name = b.company_name && a.salary = b.salary