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