Thread: Help Retrieving Latest Record
Hi, I am not sure if this went through the first time so here it is again. <br /> <p>Ok what I am trying to do is selectout 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>
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
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-0400: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
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 > > >
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
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 > > >