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