sorting duplicates in group by

Dean Lovell · September 6th, 2005, 05:37 PM

I am looking for the best way to retrieve unique records using two fields as criteria for the duplicates. I have a table with xID and yID fields (along with a timestamp and primary id) and have been using a group by on the xID and yID which results in what I want....except, I want the latest item. It gives me the earlier item, even though I order by timestamp desc...see below

SELECT primaryID
FROM table
GROUP BY xID, yID
ORDER BY timestamp DESC

This is simplified, but is basically what I want. But I am getting no effect either way on the order by. I am using mySQL 4.

I am assuming group by doesn't allow this much control? I am using group by instead of distinct because I am selecting a bunch of other fields as well in my actual query. Maybe I should be using a subselect with a distinct?

This has always been a little gray for me...

Thanks for any help!

Jeff Mason · September 7th, 2005, 01:30 PM

You need to work this from the "inside out".

Here's one way to do this. There are probably others.

Suppose you had a table in your database ('GroupTable') that contained the xID, yID, and maximum Timestamp values for each xID, yID combination.

This could be created by:

Code:

    INSERT GroupTable
    SELECT xID, yID, MAX(timestamp) as MaxDate
    FROM table
    GROUP BY xID, yID;

If this table actually did exist, you could JOIN your table to it and select
the primaryID for each resultant row:

Code:

SELECT primaryID
FROM table T1
INNER JOIN GroupTable T2
ON T1.xID = T2.xID
AND T1.yID = T2.yID
AND T1.Timestamp = T2.MaxDate;

In your case the GroupTable doesn't exist, but you can always materialize such a table by using a subquery:

Code:

SELECT primaryID
FROM table T1
INNER JOIN 
    (SELECT xID, yID, MAX(timestamp) as maxdate
    FROM table
    GROUP BY xID, yID) T2
ON T1.xID = T2.xID
AND T1.yID = T2.yID
AND T1.Timestamp = T2.MaxDate;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com