View Single Post
  #2 (permalink)  
Old September 7th, 2005, 01:30 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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