Re: Help Retrieving Latest Record

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-0400: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