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:

DepartmentEmployeeSalary
AJohn1000
AMichael1200
ABob880
BTed2000
BJane2000
CAlly2000
CKent4000

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:

DepartmentEmployeeSalary
AMichael1200
BTed2000
BJane2000
CKent4000

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.

Read More

Ruby Encoding

Published on July 16, 2014

Http Caching

Published on April 27, 2014

Password Nightmare

Published on February 13, 2007