Re: Help with query involving aggregation and joining.

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.