SQL Selecting Rows with Max Value from Groups

SQL is never hard but definitely tedious. You know what I mean.

SQL is never hard but definitely tedious. You know what I mean.

Occasionally I tend to create a relatively complicated query instead of fetching the data back and do the processing in my code. Sometimes for the performance, and sometimes just because I like challenges.

I know, I never am a SQL expert. But anyway, it might help.

Say we have a table like the following:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| A          | John     |   1000 |
| A          | Michael  |   1200 |
| A          | Bob      |    880 |
| B          | Ted      |   2000 |
| B          | Jane     |   2000 |
| C          | Ally     |   2000 |
| C          | Kent     |   4000 |
+------------+----------+--------+

And we want to get a query of all employees having the highest pay from each department. What SQL query should we have?

Well, first we need to find what the highest pay of each department is:

SELECT 
  DEP.DEPARTMENT, MAX(DEP.SALARY) 
FROM 
  DEPARTMENT DEP 
GROUP BY 
  DEP.DEPARTMENT

This will give you a list of department with its highest pay. But we do want to fetch the complete row so that we could know who that employee is. So how do we proceed from here?

We need sub query and EXISTS predicate:

SELECT * 
FROM 
  DEPARTMENT 
WHERE 
  EXISTS (
    SELECT 
      DEP.DEPARTMENT 
    FROM 
      DEPARTMENT DEP 
    GROUP BY 
      DEP.DEPARTMENT 
    HAVING 
      DEPARTMENT.DEPARTMENT = DEP.DEPARTMENT AND 
      DEPARTMENT.SALARY = MAX(DEP.SALARY)
 )

The result looks like this:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| A          | Michael  |   1200 |
| B          | Ted      |   2000 |
| B          | Jane     |   2000 |
| C          | Kent     |   4000 |
+------------+----------+--------+

The speed of this query doesn’t seem very bad. In fact, it performs far better than I originally thought since it involves sub query.