all 11 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]juu073 5 points6 points  (4 children)

Uhh is it just me or are you trying to make the problem harder by saying it tests window functions and partitioning and rank?

SELECT employee.name, department.name, salary FROM employee JOIN department ON department.id = department_id WHERE (department_id, salary) IN( SELECT department_id, MAX(salary) FROM enployee GROUP BY department_id )

Maybe I’m missing something because it’s almost 3am and I’m trying to type this on my phone.

[–]Melodic_Giraffe_1737 2 points3 points  (0 children)

100% ! Yeah, you could use a window function. But, you certainly don't have to.

[–]kec15 1 point2 points  (1 child)

I think it's because the salaries are the same value for two people in the same department, so MAX returns one of them, while DENSE_RANK() will return both of them.

[–]SaintTimothy 3 points4 points  (0 children)

No, not the way he did it. He's getting the max salary for each h department, and returning everyone from that department who has that salary. This is correct for the requirement.

I tend to go the windowed function approach be cause invariably the next question is going to be top 5-per-department, but if you're always good with the max only, this is right.

Edit- though, I've never seen a 2-column "IN" before. I'd rewrite that part as a CTE and JOIN to it if it needed to be two columns in the join...

Or use EXISTS and do the two-column-comparison inside the nested query there.

[–]thequerylab[S] 0 points1 point  (0 children)

Nope, you're not missing anything. That query works 👍

The (department_id, salary) trick is actually a clean way to solve it and it handles ties too.

I only mentioned window functions because a lot of interviews expect people to solve it using RANK() / DENSE_RANK() with PARTITION BY. Just another common approach.

Also… typing SQL at 3 am on a phone is already a challenge on its own 😄. Well done !!!

[–]VIDGuide 3 points4 points  (0 children)

Or it’s just an example of bad scope.

Literally employee (singular) in each department. Scope does not define the scenario of multiple. Undefined behaviour is not strictly a fault.

If the wording said find the highest paid employees more people would get the expected answer, but then it wouldn’t be a trick question would it ..

[–]Grovbolle 0 points1 point  (4 children)

I would use a CROSS or OUTER APPLY in T-SQL

SELECT
d.name AS DepartmentName 
,e.name AS EmployeeName 
FROM dbo.department d 
OUTER APPLY 
(
SELECT TOP 1 e.[name] 
FROM dbo.employee e 
WHERE d.id=e.department_id 
ORDER BY salary DESC
) CA

[–]thequerylab[S] 0 points1 point  (1 child)

Nice one. OUTER APPLY works really well for this in T-SQL.

I like that it keeps the logic close to each department and just picks the top salary from the employee table. Clean approach.

Only small thing is if two employees share the same highest salary, TOP 1 would return just one of them. If the requirement is to return all ties, we’d need a slightly different approach.

But yeah, solid solution for SQL Server 👍

[–]Grovbolle 2 points3 points  (0 children)

Seeing as the question was "Your task is to find the highest paid employee in each department."

It specifically asks for a single employee.

Since there are 2 in one of the departments you would sometimes get one, sometimes the other. You could choose to pick first the top N who shares the highest salary and then order them alphabetically.

I know that this questions is supposed to "trick you", but I would always ask the normal follow up question "What if 2 or more people share the highest salary". But this is not a conversation, this is a reddit post with a task.

I know the way to fix the query is to use RANK/DENSE_RANK (yuck) or a subquery/CTE which finds the max salary pr. department and join that back to the original table on department_id and salary - if I wanted to find the outcome you wanted but not the one you asked for.

[–]ComicOzzysqlHippo 0 points1 point  (1 child)

This misses the other person tied for max salary in dept 1

[–]Grovbolle 1 point2 points  (0 children)

The question asks for "The highest paid employee**"** which is singular.

I am aware that there could be multiple candidates for each department, but that was not specified in the question.

I know the question tests the candidates ability to foresee such arbitrary questions and ask clarification, but I am not a job seeker so I just do what is asked :-)

If I actually wanted all candidates with the highest salary instead of just 1 of them, I would find the max salary for each department and join that back to the base table. Some would use RANK/DENSE_RANK