Received: from propertykey.com (IDENT:root@propertykey.com [206.196.37.193]) by hub.org (8.10.1/8.10.1) with ESMTP id e7HHXJm64467 for ; Thu, 17 Aug 2000 13:33:19 -0400 (EDT) Received: from propertykey.com (gotojail.propertykey.com [206.196.37.197]) by propertykey.com (8.9.3/8.9.3) with ESMTP id MAA11508; Thu, 17 Aug 2000 12:33:03 -0500 Message-ID: <399C21CF.77C26118@propertykey.com> Date: Thu, 17 Aug 2000 12:33:03 -0500 From: Jeff Hoffmann Organization: PropertyKey.com X-Mailer: Mozilla 4.74 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: Bernie Huang CC: PGSQL-SQL , PHP-PGSQL Subject: Re: Fetch the latest log for each product References: <399C1907.ADE41FE7@ec.gc.ca> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/165 Bernie Huang wrote: > > Hi, > > I bet people have asked this question several times, but oh well, please > do anwser again. Thanks. =) > > I have a product table and a log file. > > product_tb > ----------- > prod_id > prod_name > ... > > log_tb > --------- > log_id > prod_id > cust_id > transact_date > ... > > How do I fetch the latest log for each product according to transaction > date? > > - Bernie here's how i would do it, i guess. select p.prod_name, l.prod_id, max(l.transact_date) as last_transaction from log_tb l, prod_tb p where l.prod_id=p.prod_id group by p.prod_name, l.prod_id; i think that's what you want, at least... -- Jeff Hoffmann PropertyKey.com