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.
source:https://bryantsai.com/advanced-sql-selecting-rows-with-max-value-from-groups/
