Finding the last person working on a project (grouping/having sql?)

I’m trying to figure out how to query the last person (name) who last (date) worked on a project (project).

Content TableA

     (id) | project  | name     | date
----------+----------+---------------------
        1 | ProjA    | Joe      | 1-1-2011
        2 | ProjA    | Sally    | 2-2-2011
        3 | ProjA    | Joe      | 3-3-2011
        4 | ProjB    | Sally    | 4-4-2011
        5 | ProjB    | Bert     | 5-5-2011

What I need to have as a result would be in this case:

     (id) | project  | name     | date
----------+----------+-------------
        3 | ProjA    | Joe      | 3-3-2011
        5 | ProjB    | Bert     | 5-5-2011

What I’ve got so far is:

SELECT project, max(date) FROM TableA

This will get met the project and date but not the id and name of the person.

SELECT id, project, name, max(date) FROM TableA

This will (obviously) result in the error

Column ‘id’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

I’m a bit rusty and lost.

Answer

You need a row_number() :

select a.*
from (select a.*, row_number() over (partition by a.project order by a.date desc) as seq
      from tablea a
     ) a
where a.seq = 1;

If date has ties, then you need dense_rank() instead.

Advertisement

Answer