X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 949DC475A97
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 15:37:38 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 18031-01 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 15:37:27 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id 60590475AAC
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 15:32:12 -0500 (EST)
Received: (qmail 6385 invoked by uid 500); 23 Feb 2003 20:45:42 -0000
Date: Sun, 23 Feb 2003 14:45:42 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Eddie Cheung <vampyre5@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with query involving aggregation and joining.
Message-ID: <20030223204542.GA6337@wolff.to>
Mail-Followup-To: Eddie Cheung <vampyre5@yahoo.com>, pgsql-sql@postgresql.org
References: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/514
X-Sequence-Number: 11912

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 join history
  order by course.courseid, history.submission desc;