Here are the queries which illustrate Shawn's point. Now to sleep.
-- wrong
DROP TABLE IF EXISTS o1;
CREATE TABLE o1
SELECT customerid,shipcity,MAX(shippeddate) AS latest
FROM orders
GROUP BY customerid;
-- right
DROP TABLE IF EXISTS o2;
CREATE TABLE o2
SELECT DISTINCT o1.customerid,o1.shipcity,o1.shippeddate AS latest
FROM orders o1
LEFT JOIN orders o2
ON o1.customerid=o2.customerid AND o1.shippeddate<o2.shippeddate
WHERE o1.shippeddate IS NOT NULL AND o2.customerid IS NULL
ORDER BY customerid;
-- 3 of 89 rows differ
SELECT
MIN(TableName) as TableName, customerid, shipcity, latest
FROM (
SELECT 'o1' AS TableName,customerid,shipcity,latest FROM o1
UNION ALL
SELECT 'o2' as TableName,customerid,shipcity,latest FROM o2
) AS tmp
GROUP BY customerid, shipcity,latest
HAVING COUNT(*) = 1;
PB
-----
Peter Brawley wrote:
LOL, three late nights in a row, lose that last post o' mine.
PB
-----
Shawn Green wrote:
--- Peter Brawley <[EMAIL PROTECTED]> 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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
