From: Peter Brawley
Date: April 27 2006 6:31pm
Subject: Re: How to select the last entry per item
List-Archive: http://lists.mysql.com/mysql/197412
Message-Id: <44510DF4.10602@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44510DF44935======="
--=======AVGMAIL-44510DF44935=======
Content-Type: multipart/alternative; boundary=------------070501020400050409020408
--------------070501020400050409020408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006
>
--------------070501020400050409020408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006
--------------070501020400050409020408--
--=======AVGMAIL-44510DF44935=======
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-44510DF44935=======--