Help with query involving aggregation and joining. - Mailing list pgsql-sql

Hi,

I have not been able to construct an SQL statement for
my task. What seemed like a simple query turned out to
be beyond my capability. Any help will be very much
appreciated.

My requirements involve several large tables. I have
simplied scenario into the follow two fictional tables
which describes the same requirements.


HISTORY
=======
id | courseId  | submission
---+-----------+-------------1 |  101      | 2002-01-20 2 |  102      | 2002-02-173 |  104      | 2002-04-304 |  102
 | 2002-02-225 |  104      | 2002-03-156 |  104      | 2002-01-21
 
COURSE
======  id  | name
------+----------- 101 | Physics 102 | Chemistry 103 | Biology 104 | Maths 105 | English


Basically I would like to display the latest
submission for each course in a table as shown below,
order by name of the courses.

Query Results:
==============id | courseId |  name    | submission
---------------------------------------4  |  102     | Chemisty | 2002-02-223  |  104     | Maths    | 2002-04-301  |
101    | Physics  | 2002-01-20
 

I have tried many different approach but all failed...
Thanks a lot for your help. 

Regards,
Eddie



__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com