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.
Date: Mon, 24 Feb 2003 11:16:33 -0600
Message-ID: <20030224171633.GA11072@wolff.to> (raw)
In-Reply-To: <20030224051738.74417.qmail@web80410.mail.yahoo.com>
References: <20030223204542.GA6337@wolff.to>
<20030224051738.74417.qmail@web80410.mail.yahoo.com>
On Sun, Feb 23, 2003 at 21:17:38 -0800,
Eddie Cheung <vampyre5@yahoo.com> wrote:
>
> 2) Bruno suggested the following query:
>
> 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;
>
> I have not used NATURAL JOIN before, but from what I
> know, it joins the columns with the same name. Since
> the joining columns of History and Course have
> different names, I have replace JOIN clause. Please
> let me know if I have made a mistake.
No I made a mistake and thought that courseId was used in both places.
> The modified query is:
> SELECT DISTINCT ON (course.id) course.id,
> history.id, course.name, history.submission
> FROM history JOIN course ON history.courseId =
> course.id
> ORDER BY course.id, history.submission desc;
>
> The results returned are :
> id | id | name | submission
> -----+----+-----------+------------
> 101 | 1 | Physics | 2002-01-20
> 102 | 4 | Chemistry | 2002-02-22
> 104 | 3 | Maths | 2002-04-30
>
> The problem here is that the results are not ordered
> by the submission date. If I sort by
> "history.submission" first, I get
> ERROR: SELECT DISTINCT ON expressions must match
> initial ORDER BY expressions.
> Please note that I cannot select distinct on the
> course.name either because it is NOT unique. The
> original tables are much larger, and the only unique
> column is the id.
My suggestion could be modified by making it a subselect and adding another
order by clause. You might get different performance (worse or better)
than the updated version of Josh's solution.
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: bruno@wolff.to, vampyre5@yahoo.com
Subject: Re: Help with query involving aggregation and joining.
In-Reply-To: <20030224171633.GA11072@wolff.to>
* 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