pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp Retrieving Latest Record
6+ messages / 6 participants
[nested] [flat]
* Help Retrieving Latest Record
@ 2001-02-16 14:41 Steve Meynell <steve@candata.com>
2001-02-16 20:32 ` Re: Help Retrieving Latest Record Michael Fork <mfork@toledolink.com>
2001-02-16 20:39 ` Re: Help Retrieving Latest Record Roberto Mello <rmello@cc.usu.edu>
2001-02-16 21:00 ` Re: Help Retrieving Latest Record Tom Lane <tgl@sss.pgh.pa.us>
2001-02-16 21:35 ` Re: Help Retrieving Latest Record Dorin Grunberg <dorin@visgen.com>
2001-02-16 21:49 ` Re: Help Retrieving Latest Record Jie Liang <jliang@ipinc.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 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
@ 2001-02-16 20:32 ` Michael Fork <mfork@toledolink.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 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
@ 2001-02-16 20:39 ` Roberto Mello <rmello@cc.usu.edu>
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 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
@ 2001-02-16 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
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 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
@ 2001-02-16 21:35 ` Dorin Grunberg <dorin@visgen.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 14:41 Help Retrieving Latest Record Steve Meynell <steve@candata.com>
@ 2001-02-16 21:49 ` Jie Liang <jliang@ipinc.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