From: Peter Brawley Date: April 28 2006 5:10pm Subject: Re: How to select the last entry per item List-Archive: http://lists.mysql.com/mysql/197441 Message-Id: <44524C76.2000100@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44524C777DDD=======" --=======AVGMAIL-44524C777DDD======= Content-Type: multipart/alternative; boundary=------------080900040704040900070403 --------------080900040704040900070403 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit LOL, three late nights in a row, lose that last post o' mine. PB ----- Shawn Green wrote: > --- Peter Brawley wrote: > > >> Brian >> >>> Hi, >>> >>> I hope this is the right list for this question. If not, I'm happy >>> to get help on where to post this question. Apologies in advance >>> >> if >> >>> this is an old question. >>> >>> We are designing a simple a tracking database with a table of >>> >> entries >> >>> showing the current location of each item in the system. Something >>> >>> simple like this. >>> >>> Table_xyz >>> item_id | location | status | time_stamp >>> >>> As the items move new time stamped entries are added to the >>> >> database. >> >>> How would you query to find the current location of all the items >>> currently in the system. As you might expect we don't want to >>> >> replace >> >>> the entry for an item when a location update is made because we >>> >> need >> >>> to keep the history. We plan on removing items after a suitable >>> >> delay >> >>> when they reach their destination. >>> >> An item is in the most recent location for that item_id, right? Then >> ... >> >> SELECT item_id, location,MAX(timestamp) >> FROM table_xyz >> GROUP BY item_id; >> >> PB >> >>> Thanks >>> >>> -Brian >>> >>> >>> > Peter, > > I am surprised at you ;-) You should know that the query you sent > won't work like you said. Here's your query suggestion: > > >> SELECT item_id, location,MAX(timestamp) >> FROM table_xyz >> GROUP BY item_id; >> > > The item_id will be unique (thanks to the GROUP BY item_id) and the > MAX(timestamp) will also be correct (again because of the GROUP BY) but > the middle column, `location`, will not necessarily be the location > code of the record with the MAX(timestamp) :-( > > Because that column is neither part of the GROUP BY clause or covered > by an aggregate function, the engine should throw an error. However, > MySQL tries to be nicer than that so it just picks a random value from > all of the rows where the item_id's are the same. The only way to get > to the groupwize maximum (in this case the record with the latest date > from a group of records sharing the same ID) is by using one of the > techniques listed here (temp table, subquery, concat hack): > > http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html > > Shawn Green > Database Administrator > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > --------------080900040704040900070403 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit LOL, three late nights in a row, lose that last post o' mine.

PB

-----

Shawn Green wrote:

--- Peter Brawley <peter.brawley@stripped> wrote:

Brian

Hi,

I hope this is the right list for this question. If not, I'm happy
to get help on where to post this question.  Apologies in advance

if

this is an old question.

We are designing a simple a tracking database with a table of

entries

showing the current location of each item in the system.  Something

simple like this.

Table_xyz
item_id | location | status | time_stamp

As the items move new time stamped entries are added to the

database.

How would you query to find the current location of all the items 
currently in the system.  As you might expect we don't want to

replace

the entry for an item when a location update is made because we

need

to keep the history.  We plan on removing items after a suitable

delay

when they reach their destination.

An item is in the most recent location for that item_id, right? Then
...

SELECT item_id, location,MAX(timestamp)
FROM table_xyz
GROUP BY item_id;

PB

Thanks

-Brian

Peter, 

I am surprised at you ;-)  You should know that the query you sent
won't work like you said. Here's your query suggestion:

SELECT item_id, location,MAX(timestamp)
FROM table_xyz
GROUP BY item_id;


The item_id will be unique (thanks to the GROUP BY item_id) and the
MAX(timestamp) will also be correct (again because of the GROUP BY) but
the middle column, `location`, will not necessarily be the location
code of the record with the MAX(timestamp) :-(

Because that column is neither part of the GROUP BY clause or covered
by an aggregate function, the engine should throw an error. However,
MySQL tries to be nicer than that so it just picks a random value from
all of the rows where the item_id's are the same.  The only way to get
to the groupwize maximum (in this case the record with the latest date
from a group of records sharing the same ID) is by using one of the
techniques listed here (temp table, subquery, concat hack):

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

Shawn Green
Database Administrator

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

--------------080900040704040900070403-- --=======AVGMAIL-44524C777DDD======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 --=======AVGMAIL-44524C777DDD=======--