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
