this might be a simple query?

[chiprivers]

I have not really ventured much past basic query statements before so please excuse me if what I am trying to do here is very simple.

 

I have a table with columns including 'id', 'status', 'time', 'user', 'created'.

 

There will be many records where the 'time' and 'user' columns have duplicate values but will have a DATETIME value in the 'created' column showing when the record was created.

 

I need to pull all records from the above table where 'user' = $user, but if there are multiple entries with the same value in the 'time' column, only the most recent value should be returned.

 

Could somebody please give me a SELECT statement for this?

[kickstart]

Hi

 

Something like this.

 

SELECT *
FROM someTable a
INNER JOIN (SELECT time, user, MAX(created) AS MaxCreated FROM someTable WHERE user = $User GROUP BY time, user) b
ON a.time = b.time AND a.user = b.user AND a.created = b.MaxCreated

 

This is assuming that user is a numeric field. It isn't perfect if there is a possibility that created isn't unique for a time and user (if id is unique and in created order then it might be better to match on id and max (id)).

 

All the best

 

Keith