Re: Help with query involving aggregation and joining.

On Sun, Feb 23, 2003 at 11:02:27 -0800,
Eddie Cheung <vampyre5(at)yahoo(dot)com> wrote:
>
> HISTORY
> =======
> id | courseId | submission
> ---+-----------+-------------
> 1 | 101 | 2002-01-20
> 2 | 102 | 2002-02-17
> 3 | 104 | 2002-04-30
> 4 | 102 | 2002-02-22
> 5 | 104 | 2002-03-15
> 6 | 104 | 2002-01-21
>
>
> COURSE
> ======
> id | name
> ------+-----------
> 101 | Physics
> 102 | Chemistry
> 103 | Biology
> 104 | Maths
> 105 | English
>
>
> Basically I would like to display the latest
> submission for each course in a table as shown below,
> order by name of the courses.
>
> Query Results:
> ==============
> id | courseId | name | submission
> ---------------------------------------
> 4 | 102 | Chemisty | 2002-02-22
> 3 | 104 | Maths | 2002-04-30
> 1 | 101 | Physics | 2002-01-20

I think you want to do something like:

select distinct on (course.courseid)
history.id, course.courseid, course.name, history.submission
from course natural join history
order by course.courseid, history.submission desc;