From steve@candata.com Fri Feb 16 19:24:15 2001 Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f1GJOFx46846 for ; Fri, 16 Feb 2001 14:24:15 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from grunt.candata.com (gateway.candata.com [204.225.91.106]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1FL1tx34609 for ; Thu, 15 Feb 2001 16:01:55 -0500 (EST) (envelope-from steve@candata.com) Received: from candata.com (steve@grunt.candata.com [192.168.1.15]) by grunt.candata.com (8.9.3/8.9.3) with ESMTP id QAA03815 for ; Thu, 15 Feb 2001 16:04:57 -0500 Message-ID: <3A8C4471.CFDF9937@candata.com> Date: Thu, 15 Feb 2001 16:04:50 -0500 From: Steve Meynell Organization: CanData X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.5-15 i586) X-Accept-Language: en MIME-Version: 1.0 To: Postgresql Mailing List Subject: Help retrieving lastest record Content-Type: multipart/alternative; boundary="------------B142BCC3AC2D8EC6FC7FA0F7" X-Archive-Number: 200102/289 X-Sequence-Number: 1482 --------------B142BCC3AC2D8EC6FC7FA0F7 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ok what I am trying to do is select out of the database the latest record meeting a certain criteria. Example: Number | Fruit | Date 15 Apples July 20, 1999 20 Oranges June 7, 2000 13 Pears Jan 31, 2000 17 Apples April 10, 1999 Now what I need to do is select the oranges out because the date is the latest one, something like: select * from basket where max(date); This would yield me: 20 Oranges June 7, 2000 I know this doesn't work but I need something like it. or something like select * from basket where max(date) and fruit='Apples'; This would yield me: 15 Apples July 20, 1999 Thank you in advance, -- Steve Meynell Candata Systems --------------B142BCC3AC2D8EC6FC7FA0F7 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Ok what I am trying to do is select out of the database the latest record meeting a certain criteria.

Example:

Number             |        Fruit         |     Date
15                                Apples          July 20, 1999
20                                Oranges        June 7, 2000
13                                 Pears            Jan 31, 2000
17                                 Apples         April 10, 1999
Now what I need to do is select the oranges out because the date is the latest one, something like:

select * from basket where max(date);
This would yield me:
20                                Oranges        June 7, 2000

I know this doesn't work but I need something like it.
or something like

select * from basket where max(date) and fruit='Apples';
This would yield me:
15                                Apples          July 20, 1999

Thank you in advance,
 

-- 
Steve Meynell
Candata Systems
  --------------B142BCC3AC2D8EC6FC7FA0F7-- From aperrin@socrates.berkeley.edu Fri Feb 16 20:50:07 2001 Received: from cecil.schsr.unc.edu (cecil.schsr.unc.edu [152.2.35.82]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GKo7x60685 for ; Fri, 16 Feb 2001 15:50:07 -0500 (EST) (envelope-from aperrin@socrates.berkeley.edu) Received: from nujoma.perrins (mail@rover4.schsr.unc.edu [152.2.35.147]) by cecil.schsr.unc.edu (8.10.1/8.10.1) with ESMTP id f1GKkK014071; Fri, 16 Feb 2001 15:46:21 -0500 Received: from aperrin (helo=localhost) by nujoma.perrins with local-esmtp (Exim 3.12 #1 (Debian)) id 14Trmb-0005KX-00; Fri, 16 Feb 2001 15:47:13 -0500 Date: Fri, 16 Feb 2001 15:47:13 -0500 (EST) From: Andrew Perrin To: Steve Meynell cc: Postgresql Mailing List Subject: Re: Help retrieving lastest record In-Reply-To: <3A8C4471.CFDF9937@candata.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200102/293 X-Sequence-Number: 1487 How 'bout these: fgdata=# select * from fruit order by dt desc limit 1; number | fruit | dt --------+---------+------------------------ 20 | Oranges | 2000-06-07 00:00:00-05 (1 row) fgdata=# select * from fruit where fruit='Apples' order by dt desc limit 1; number | fruit | dt --------+--------+------------------------ 15 | Apples | 1999-07-20 00:00:00-05 (1 row) Cheers, Andy Perrin ---------------------------------------------------------------------- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin aperrin@socrates.berkeley.edu - aperrin@igc.apc.org On Thu, 15 Feb 2001, Steve Meynell wrote: > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number | Fruit | Date > 15 Apples July 20, 1999 > 20 Oranges June 7, 2000 > 13 Pears Jan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20 Oranges June 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15 Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > > From alex@pilosoft.com Fri Feb 16 20:48:36 2001 Received: from spider.pilosoft.com (p55-222.acedsl.com [160.79.55.222]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GKmax60173 for ; Fri, 16 Feb 2001 15:48:36 -0500 (EST) (envelope-from alex@pilosoft.com) Received: from localhost (alexmail@localhost) by spider.pilosoft.com (8.9.3/8.9.3) with ESMTP id PAA15000; Fri, 16 Feb 2001 15:50:32 -0500 (EST) Date: Fri, 16 Feb 2001 15:50:32 -0500 (EST) From: Alex Pilosov To: Steve Meynell cc: Postgresql Mailing List Subject: Re: Help retrieving lastest record In-Reply-To: <3A8C4471.CFDF9937@candata.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200102/290 X-Sequence-Number: 1483 Get an SQL book. select * from basket where date=(select max(date) where fruit='Apples) and fruit='Apples'; On Thu, 15 Feb 2001, Steve Meynell wrote: > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number | Fruit | Date > 15 Apples July 20, 1999 > 20 Oranges June 7, 2000 > 13 Pears Jan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20 Oranges June 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15 Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >