Re: Help with query involving aggregation and joining.

Eddie,

> 1) Josh suggested the following query. (I have made
> minor changes by adding the table name to each field)
>
>    SELECT history.id, history.courseId, course.name,
> MAX(history.submission) AS submission
>    FROM history JOIN course ON history.courseId =
> course.Id
>    GROUP BY history.id, history.courseId, course.name
>    ORDER BY course.name;
>
> The results returned are:
>  id | courseid |   name    | submission
> ----+----------+-----------+------------
>   2 |      102 | Chemistry | 2002-02-17
>   4 |      102 | Chemistry | 2002-02-22
>   3 |      104 | Maths     | 2002-04-30
>   5 |      104 | Maths     | 2002-03-15
>   6 |      104 | Maths     | 2002-01-21
>   1 |      101 | Physics   | 2002-01-20

Sorry, knew I was making it too simple.  Try:
  SELECT history.id, history.courseId, course.name, submission  FROM history JOIN course ON history.courseId =
course.IdJOIN (select course_id, max(submission) as sub_max    FROM history GROUP BY course_id) hmaxON
(history.course_id= hmax.course_id AND history.submission = hmax.sub_max)  GROUP BY history.id, history.courseId,
course.name ORDER BY course.name; 


--
Josh Berkus
Aglio Database Solutions
San Francisco