Re: Help with query involving aggregation and joining. - Mailing list pgsql-sql

On Sun, Feb 23, 2003 at 11:02:27 -0800, Eddie Cheung <vampyre5@yahoo.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
joinhistory order by course.courseid, history.submission desc;