SQL Nugget

I am an ardent fan of Joe Celko - goes way back to the early 90s and I am always looking for ways of horning on my SQL skills.  Who is Joe Celko? Well if you are into SQL and you've not heard or come across anything about Joe Celko, then, you must be from another planet!

Well Joe Celko - in my view- is one of those PURISTS  tyring really hard to make SQL uniform accross board irrespective of the VENDOR. Simply put - any SQL Statement you can run against an ORACLE DB should be valid for a SQL SERVER db as well.  He tends to bring analytical reasoning into SQL and, again, in my view, one of the few mentors still around in the area of SQL.

Anyway, I digress, but it is always good to stimulate the interest of the reader before delving straight to the issue at hand. The thrust of this article is that I came across a POST on the web and I was awe struck as to the simplicity as well as  the efficacy of the proposed solution.

The problem was - in a set of rows assocated to an entity - find the row with the highest value of a column for a date range. Translate to real life - Assuming you have a table of Employees with daily number of hours worked and  date columns. Find the Last Date worked by Employee as well as the hours.

Sample Data:

Employee ID	Date	Number Of Hours
0001	11/01/2011	5
0002	03/03/2009	6
0002	03/03/2011	4
0004	03/03/2011	4
0005	03/03/2011	4
0004	03/04/2011	4
0001	11/01/2012	5

The Desired Result is

Employee ID	Date	Number Of Hours
0001	11/01/2012	5
0002	03/03/2011	4
0004	03/04/2011	4
0005	03/03/2011	4

The initial reaction will be to write the SQL for it as thus :

SELECT  emp_id, MAX(date), Max(hours)
  FROM  employee
  GROUP BY date, hours

Unfortunately, the resultset is not the desired one -

Employee ID	Date	Number Of Hours
0001	11/01/2012	5
0002	03/03/2011	6
0004	03/04/2011	4
0005	03/03/2011	4

A possible way to write the correct SQL is this  :

SELECT emp_id, date, hours
  FROM  employee a
  WHERE   a.date  = ( SELECT MAX(b.date)
                        FROM employee b
                       WHERE b.emp_id = a.emp_id)

This will return the desired resultset.

However, there is a simpler way - at least from my perspective, to do this.

SELECT emp_id,date,hours
  FROM employee a
       LEFT JOIN employee b ON ( b.empl_id = a.emp_id AND a.date < b.date)
WHERE  b.empl_id IS NULL

Anyway, if you are interested in reading more about this solution and where I came across it, please check it out here "Solution Source".