From: Josh Berkus <josh@agliodbs.com>
To: vampyre5@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Help with query involving aggregation and joining.
Date: Sun, 23 Feb 2003 22:46:05 -0800
Message-ID: <200302232246.05035.josh@agliodbs.com> (raw)
In-Reply-To: <20030224051738.74417.qmail@web80410.mail.yahoo.com>
References: <20030224051738.74417.qmail@web80410.mail.yahoo.com>
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 =
> 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 =
course.Id
JOIN (select course_id, max(submission) as sub_max
FROM history GROUP BY course_id) hmax
ON (history.course_id = hmax.course_id AND history.submission = hmax.sub_max)
GROUP BY history.id, history.courseId, course.name
ORDER BY course.name;
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, vampyre5@yahoo.com
Subject: Re: Help with query involving aggregation and joining.
In-Reply-To: <200302232246.05035.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox