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.Id
JOIN (select course_id, max(submission) as sub_max
FROM history GROUP BY course_id) hmax
ON (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