X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 14CA8475A71
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 02:55:20 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 11331-01 for <pgsql-sql@postgresql.org>;
	Mon, 24 Feb 2003 02:55:08 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id DB9BB475B8F
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 01:46:53 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2854317; Sun, 23 Feb 2003 22:46:52 -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 22:46:05 -0800
User-Agent: KMail/1.4.3
References: <20030224051738.74417.qmail@web80410.mail.yahoo.com>
In-Reply-To: <20030224051738.74417.qmail@web80410.mail.yahoo.com>
MIME-Version: 1.0
Message-Id: <200302232246.05035.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/526
X-Sequence-Number: 11924

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 =3D
> 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 =3D
course.Id
	JOIN (select course_id, max(submission) as sub_max
		FROM history GROUP BY course_id) hmax
	ON (history.course_id =3D hmax.course_id AND history.submission =3D hmax.s=
ub_max)
   GROUP BY history.id, history.courseId, course.name
   ORDER BY course.name;


--=20
Josh Berkus
Aglio Database Solutions
San Francisco