View Single Post
  #1 (permalink)  
Old September 6th, 2005, 05:37 PM
Dean Lovell Dean Lovell is offline
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default sorting duplicates in group by

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!