Thread: Help retrieving lastest record

Ok what I am trying to do is select out of the database the latest record meeting a certain criteria. <p>Example:
<p>Number            |        Fruit         |     Date <br />15                                Apples          July 20,
1999<br />20                                Oranges        June 7, 2000 <br />13                                
Pears           Jan 31, 2000 <br />17                                 Apples         April 10, 1999 <br />Now what I
needto do is select the oranges out because the date is the latest one, something like: <p>select * from basket where
max(date);<br />This would yield me: <br />20                                Oranges        June 7, 2000 <p>I know this
doesn'twork but I need something like it. <br />or something like <p>select * from basket where max(date) and
fruit='Apples';<br />This would yield me: <br />15                                Apples          July 20, 1999
<p>Thankyou in advance, <br />  <pre>-- 
 
Steve Meynell
Candata Systems</pre>  

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

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