Re: Help with query involving aggregation and joining.

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.