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 <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
>>
>>
Attachment: [text/html]
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
