From steve@candata.com Fri Feb 16 19:29:55 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 f1GJTtx47888 for ; Fri, 16 Feb 2001 14:29:55 -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 f1GEc6x37139 for ; Fri, 16 Feb 2001 09:38:06 -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 JAA04971 for ; Fri, 16 Feb 2001 09:41:17 -0500 Message-ID: <3A8D3C04.3D81B5BA@candata.com> Date: Fri, 16 Feb 2001 09:41:09 -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 Latest Record Content-Type: multipart/alternative; boundary="------------0C98608641A89F5AEFB1A40F" X-Archive-Number: 200102/289 X-Sequence-Number: 1481 --------------0C98608641A89F5AEFB1A40F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi, I am not sure if this went through the first time so here it is again. 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 --------------0C98608641A89F5AEFB1A40F Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi,  I am not sure if this went through the first time so here it is again.
 

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
  --------------0C98608641A89F5AEFB1A40F-- From mfork@toledolink.com Fri Feb 16 20:32:25 2001 Received: from glass.toledolink.com (glass.toledolink.com [205.133.127.8]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GKWPx58109 for ; Fri, 16 Feb 2001 15:32:25 -0500 (EST) (envelope-from mfork@toledolink.com) Received: from localhost (mfork@localhost) by glass.toledolink.com (8.10.2/8.10.2) with ESMTP id f1GKW3o24009; Fri, 16 Feb 2001 15:32:03 -0500 (EST) Date: Fri, 16 Feb 2001 15:32:03 -0500 (EST) From: Michael Fork To: Steve Meynell cc: Postgresql Mailing List Subject: Re: Help Retrieving Latest Record In-Reply-To: <3A8D3C04.3D81B5BA@candata.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200102/292 X-Sequence-Number: 1486 SELECT * FROM basket ORDER BY date desc LIMIT 1; and SELECT * FROM basket WHERE fruit = 'apple' ORDER BY date desc LIMIT 1; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > 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 rmello@cc.usu.edu Fri Feb 16 20:41:56 2001 Received: from fslc.usu.edu (fslc.usu.edu [129.123.2.40]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GKfsx59159 for ; Fri, 16 Feb 2001 15:41:56 -0500 (EST) (envelope-from rmello@fslc.usu.edu) Received: (from rmello@localhost) by fslc.usu.edu (8.11.2/8.11.2/Debian 8.11.2-1) id f1GKdw415392; Fri, 16 Feb 2001 13:39:58 -0700 Date: Fri, 16 Feb 2001 13:39:58 -0700 From: Roberto Mello To: Steve Meynell Cc: Postgresql Mailing List Subject: Re: Help Retrieving Latest Record Message-ID: <20010216133958.A15334@cc.usu.edu> References: <3A8D3C04.3D81B5BA@candata.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.12i In-Reply-To: <3A8D3C04.3D81B5BA@candata.com>; from steve@candata.com on Fri, Feb 16, 2001 at 09:41:09AM -0500 X-Archive-Number: 200102/291 X-Sequence-Number: 1485 On Fri, Feb 16, 2001 at 09:41:09AM -0500, Steve Meynell wrote: > > 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 Maybe you mean min? This is the earliest date you had in the set. Assuming the Date column is of datetime/timestamp type: test=# \d fruits Table "fruits" Attribute | Type | Modifier -----------+-----------------------+---------- fruit_id | integer | name | character varying(50) | date | timestamp | test=# select * from fruits; fruit_id | name | date ----------+-----------+------------------------ 1 | Orange | 2001-02-16 13:26:52-07 3 | Pineapple | 1999-10-04 00:00:00-06 2 | Apple | 2000-12-05 00:00:00-07 4 | Apple | 2000-07-01 00:00:00-06 (4 rows) test=# SELECT * FROM fruits WHERE name='Apple' ORDER BY date DESC LIMIT 1; fruit_id | name | date ----------+-------+------------------------ 2 | Apple | 2000-12-05 00:00:00-07 (1 row) Look at the documentation for the ORDER BY and LIMIT clauses on the PG docs. -Roberto -- Computer Science Utah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto From tgl@sss.pgh.pa.us Fri Feb 16 21:01:03 2001 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GL13x63593 for ; Fri, 16 Feb 2001 16:01:03 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f1GL0v913703; Fri, 16 Feb 2001 16:00:57 -0500 (EST) To: Steve Meynell cc: Postgresql Mailing List Subject: Re: Help Retrieving Latest Record In-reply-to: <3A8D3C04.3D81B5BA@candata.com> References: <3A8D3C04.3D81B5BA@candata.com> Comments: In-reply-to Steve Meynell message dated "Fri, 16 Feb 2001 09:41:09 -0500" Date: Fri, 16 Feb 2001 16:00:56 -0500 Message-ID: <13700.982357256@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200102/294 X-Sequence-Number: 1488 Steve Meynell writes: > select * from basket where max(date) and fruit='Apples'; > I know this doesn't work but I need something like it. Perhaps select * from basket where fruit='Apples' order by date desc limit 1; The limit clause is not standard SQL, but it's a pretty easy answer if portability doesn't bother you... regards, tom lane From dorin@visgen.com Fri Feb 16 20:38:33 2001 Received: from mail.visgen.com (uu-t1-6.visgen.com [216.94.71.6]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GKcXx58791 for ; Fri, 16 Feb 2001 15:38:33 -0500 (EST) (envelope-from dorin@visgen.com) Received: from dorin (bay-auto-121 [10.1.18.121]) by mail.visgen.com (8.9.3/8.9.3) with ESMTP id PAA01912; Fri, 16 Feb 2001 15:37:15 -0500 (EST) Message-Id: <4.2.0.58.20010216153436.00ab6ad0@pophost> X-Sender: dorin@pophost X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.0.58 Date: Fri, 16 Feb 2001 15:35:44 -0600 To: Steve Meynell , Postgresql Mailing List From: Dorin Grunberg Subject: Re: Help Retrieving Latest Record In-Reply-To: <3A8D3C04.3D81B5BA@candata.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200102/291 X-Sequence-Number: 1484 It would be something like: select * from basket where Date=(SELECT max(Date) from basket); At 09:41 AM 2/16/2001 -0500, Steve Meynell wrote: >Hi, I am not sure if this went through the first time so here it is again. > > >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 jliang@ipinc.com Fri Feb 16 21:50:09 2001 Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1GLo9x70734 for ; Fri, 16 Feb 2001 16:50:09 -0500 (EST) (envelope-from jliang@tidal.ipinc.com) Received: from localhost (jliang@localhost) by tidal.ipinc.com (8.9.3/8.9.3) with ESMTP id NAA71084; Fri, 16 Feb 2001 13:49:16 -0800 (PST) (envelope-from jliang@tidal.ipinc.com) Date: Fri, 16 Feb 2001 13:49:16 -0800 (PST) From: Jie Liang To: Steve Meynell cc: Postgresql Mailing List Subject: Re: Help Retrieving Latest Record In-Reply-To: <3A8D3C04.3D81B5BA@candata.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200102/296 X-Sequence-Number: 1490 Subquery will do: select * from basket where Date in (select max(Date) from basket); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > 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 > > >