X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2365F475D3F
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 15:28:13 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 16671-01 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 15:28:02 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 0DB52475B8D
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 15:25:32 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2853771; Sun, 23 Feb 2003 12:25:31 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: vampyre5@yahoo.com, pgsql-sql@postgresql.org
Subject: Re: Help with query involving aggregation and joining.
Date: Sun, 23 Feb 2003 12:24:47 -0800
User-Agent: KMail/1.4.3
References: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
In-Reply-To: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
MIME-Version: 1.0
Message-Id: <200302231224.47583.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/513
X-Sequence-Number: 11911

Eddie,

> My requirements involve several large tables. I have
> simplied scenario into the follow two fictional tables
> which describes the same requirements.

Keep in mind that a simplified example may not solve your real problem ...

> 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:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>  id | courseId |  name    | submission
> ---------------------------------------
>  4  |  102     | Chemisty | 2002-02-22
>  3  |  104     | Maths    | 2002-04-30
>  1  |  101     | Physics  | 2002-01-20

Easy:

SELECT id, courseid, name, max(submission) as submission
FROM history JOIN courses ON history.courseid =3D course.id
GROUP BY id, courseid, name
ORDER BY name

And as such, I suspect that your real case is more complicated than the abo=
ve=20
...

--=20
Josh Berkus
Aglio Database Solutions
San Francisco