He Guys, here's 3 different methods to do the same thing, I'm curious to
know which method ppl think is the best. In this example I want to get the
employee who has the maximum salary in each department. If I simply use the
max function then I would only be able to get the maximum salary, not the
employee name, so a bit of a workaround is needed. Opinions?

Cheers,
Michael


CREATE TABLE #People (Name VARCHAR(20), Salary INT, Dept VARCHAR(20))
INSERT INTO #People VALUES ('John', 200000, 'Sales')
INSERT INTO #People VALUES ('Swee', 220000, 'Sales')
INSERT INTO #People VALUES ('Pete', 220000, 'HR')
INSERT INTO #People VALUES('Jenny', 240000, 'HR')


;WITH Y AS (
SELECT Name, Salary, MAX(Salary) OVER (PARTITION BY Dept) AS MaxSalary,
Dept
FROM #People
)
SELECT Name, Salary, Dept FROM Y
WHERE Salary = MaxSalary
ORDER BY Name



SELECT * FROM #People AS P1
WHERE NOT EXISTS (SELECT * FROM #People AS P2 WHERE P2.Salary > P1.Salary
AND P2.Dept = P1.Dept)
ORDER BY Name

;WITH Y AS (
SELECT Dept, MAX(Salary) AS MaxSalary FROM #People GROUP BY Dept
)
SELECT #People.* FROM #People
JOIN Y ON #People.Dept = Y.Dept AND #People.Salary = Y.MaxSalary
ORDER BY Name

DROP TABLE #People

Michael

I think WHERE NOT EXISTS is a good one
Another is
SELECT * FROM #People

WHERE Salary=(SELECT MAX(Salary) FROM #People P WHERE

P.Dept=#People.Dept)





"Michael C" <mike (AT) nospam (DOT) com> wrote

The following should be better than the 3 methods:

WITH Y AS (
SELECT Name, Dept, Salary, DENSE_RANK() OVER(PARTITION BY Dept ORDER BY Salary DESC) AS rk
FROM #People)
SELECT Name, Dept, Salary
FROM Y
WHERE rk = 1;

This can benefit from covering index or at least index on the partitioning and ordering columns.

--
Plamen Ratchev
http://www.SQLStudio.com

"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote

Thanks Uri and Plamen, is there any reason you use DENSE_RANK over RANK?

Michael

Not really, both RANK and DENSE_RANK will work fine in this case as only rank 1 is needed.

--
Plamen Ratchev
http://www.SQLStudio.com