Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5EC3ME57160 for ; Thu, 14 Jun 2001 08:03:22 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 2.12 #1) id 15AVr3-0000kE-0W; Thu, 14 Jun 2001 13:04:05 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 4269314D4E; Thu, 14 Jun 2001 13:02:58 +0100 (BST) Message-ID: <005701c0f4c9$f36d3c00$1001a8c0@archonet.com> From: "Richard Huxton" To: "Hunter, Ray" , "'Postres-sql' (E-mail)" References: <59358A738F45D51186A30008C74CE2500A079E@slc-exc1.ctron.com> Subject: Re: Getting a specific row from a table Date: Thu, 14 Jun 2001 13:02:52 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/206 X-Sequence-Number: 3464 From: "Hunter, Ray" > My problem is that I want to pull to specific rows from a query result. > > First here is the query: > SQL-query: > select card, status, time_stamp, comp_date > from test_record > where id = 45 > order by card, comp_date > > What I want is the two rows that are bold. However this list will continue > to grow and have more card types. I always want the last card type in the > card group, because this has the comp_date that I am looking for. I'm assuming here that id,card,time_stamp can't have duplicates and that you want the most recent time_stamp for a specific id,card. SELECT id,card,status,time_stamp,comp_date FROM cards c1 WHERE c1.id=45 AND c1.time_stamp = (SELECT max(time_stamp) FROM cards c2 WHERE c2.id=c1.id AND c2.card=c1.card); What we're doing here is only selecting records where the current time_stamp matches the maximum time_stamp for a specific id/card. If you have duplicate time_stamp values for a specific id/card this won't work. If this is too slow, use a temporary table to assemble id,card,max(time_stamp) and join to the temporary table. HTH - Richard Huxton