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:
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
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:
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.