Message-ID: <20030223190227.21867.qmail@web80401.mail.yahoo.com>
Date: Sun, 23 Feb 2003 11:02:27 -0800 (PST)
From: Eddie Cheung <vampyre5@yahoo.com>
Reply-To: vampyre5@yahoo.com
Subject: Help with query involving aggregation and joining.
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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-17
 3 |  104      | 2002-04-30
 4 |  102      | 2002-02-22
 5 |  104      | 2002-03-15
 6 |  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-22
 3  |  104     | Maths    | 2002-04-30
 1  |  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