pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help retrieving lastest record
3+ messages / 3 participants
[nested] [flat]

* Help retrieving lastest record
@ 2001-02-15 21:04  Steve Meynell <steve@candata.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Steve Meynell @ 2001-02-15 21:04 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Help retrieving lastest record
@ 2001-02-16 20:47  Andrew Perrin <aperrin@socrates.berkeley.edu>
  parent: Steve Meynell <steve@candata.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew Perrin @ 2001-02-16 20:47 UTC (permalink / raw)
  To: Steve Meynell <steve@candata.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Help retrieving lastest record
@ 2001-02-16 20:50  Alex Pilosov <alex@pilosoft.com>
  parent: Steve Meynell <steve@candata.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Alex Pilosov @ 2001-02-16 20:50 UTC (permalink / raw)
  To: Steve Meynell <steve@candata.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-02-16 20:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-15 21:04 Help retrieving lastest record Steve Meynell <steve@candata.com>
2001-02-16 20:47 ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-02-16 20:50 ` Alex Pilosov <alex@pilosoft.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox