pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help Retrieving Latest Record
6+ messages / 6 participants
[nested] [flat]

* Help Retrieving Latest Record
@ 2001-02-16 14:41  Steve Meynell <steve@candata.com>
  0 siblings, 5 replies; 6+ messages in thread

From: Steve Meynell @ 2001-02-16 14:41 UTC (permalink / raw)
  To: pgsql-sql

Hi,  I am not sure if this went through the first time so here it is
again.


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] 6+ messages in thread

* Re: Help Retrieving Latest Record
@ 2001-02-16 20:32  Michael Fork <mfork@toledolink.com>
  parent: Steve Meynell <steve@candata.com>
  4 siblings, 0 replies; 6+ messages in thread

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

SELECT * FROM basket ORDER BY date desc LIMIT 1;
and
SELECT * FROM basket WHERE fruit = 'apple' ORDER BY date desc LIMIT 1;

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Fri, 16 Feb 2001, Steve Meynell wrote:

> Hi,  I am not sure if this went through the first time so here it is
> again.
> 
> 
> 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] 6+ messages in thread

* Re: Help Retrieving Latest Record
@ 2001-02-16 20:39  Roberto Mello <rmello@cc.usu.edu>
  parent: Steve Meynell <steve@candata.com>
  4 siblings, 0 replies; 6+ messages in thread

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

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



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

* Re: Help Retrieving Latest Record
@ 2001-02-16 21:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Steve Meynell <steve@candata.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2001-02-16 21:00 UTC (permalink / raw)
  To: Steve Meynell <steve@candata.com>; +Cc: pgsql-sql

Steve Meynell <steve@candata.com> writes:
> select * from basket where max(date) and fruit='Apples';
> I know this doesn't work but I need something like it.

Perhaps

	select * from basket where fruit='Apples'
		order by date desc
		limit 1;

The limit clause is not standard SQL, but it's a pretty easy answer
if portability doesn't bother you...

			regards, tom lane



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

* Re: Help Retrieving Latest Record
@ 2001-02-16 21:35  Dorin Grunberg <dorin@visgen.com>
  parent: Steve Meynell <steve@candata.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Dorin Grunberg @ 2001-02-16 21:35 UTC (permalink / raw)
  To: Steve Meynell <steve@candata.com>; pgsql-sql

It would be something like:

select * from basket where Date=(SELECT max(Date) from basket);



At 09:41 AM 2/16/2001 -0500, Steve Meynell wrote:
>Hi,  I am not sure if this went through the first time so here it is again.
>
>
>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] 6+ messages in thread

* Re: Help Retrieving Latest Record
@ 2001-02-16 21:49  Jie Liang <jliang@ipinc.com>
  parent: Steve Meynell <steve@candata.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Jie Liang @ 2001-02-16 21:49 UTC (permalink / raw)
  To: Steve Meynell <steve@candata.com>; +Cc: pgsql-sql


Subquery will do:

select * from basket where Date in (select max(Date) from basket);

Jie LIANG

St. Bernard Software
Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Fri, 16 Feb 2001, Steve Meynell wrote:

> Hi,  I am not sure if this went through the first time so here it is
> again.
> 
> 
> 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] 6+ messages in thread


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

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-16 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
2001-02-16 20:32 ` Michael Fork <mfork@toledolink.com>
2001-02-16 20:39 ` Roberto Mello <rmello@cc.usu.edu>
2001-02-16 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 21:35 ` Dorin Grunberg <dorin@visgen.com>
2001-02-16 21:49 ` Jie Liang <jliang@ipinc.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