From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Eddie Cheung <vampyre5(at)yahoo(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Help with query involving aggregation and joining. |
Date: | 2003-02-24 17:16:33 |
Message-ID: | 20030224171633.GA11072@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, Feb 23, 2003 at 21:17:38 -0800,
Eddie Cheung <vampyre5(at)yahoo(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | no.spam | 2003-02-24 17:18:24 | Denormalizing during select |
Previous Message | mallah | 2003-02-24 15:51:37 | Re: Help with query involving aggregation and joining. |