mysql时间按照日期group,MySQL Group按ID和最新日期时间

I saw similar type of questions here but none of them helped my situation.

I have a table lets say Test (using mysql MyISAM)

Test has the following schema:

TID INT PRIMARY KEY AUTO_INCREMENT

TData varchar (data that i want to select)

TUserID INT (this will be joined with users table, same users can have many records here)

TViewedAt DATETIME (records each time user visits page, datetime format)

Now each time a test page is visited by currently logged in user, it adds records to this table recording the user ID whoever visited, some data and date and time that user visited with TID being auto incremented each time.

Then i have a backend manage page where i can see which user visited test page, how many times, data and when. Basically its a table with list of 20-25 latest rows. My Query should only select one record per data. Data can be same for user but at different time but i only want to select one data for each user which is the most latest. So if user visited test page 10 times, 10 records go to database but displays only 1 latest record on the table. If another user visits page 15 times it displays 1 record for this second user which is the latest one again so in total now we have 2 rows displaying on the table. I got everything to work but GROUP BY for some reason and MAX(date) does not give me the latest datetime for each date.

here is my query:

SELECT *

FROM Test

WHERE TUserID = 123

GROUP BY TData

ORDER BY TViewedAt

returns 2 rows out of which none of those rows are the latest.

thanks alot

解决方案

I think that with the below you can achieve what you need, These operation are so called "Group-wise Maximum".

Option 1

This is the easiest to understand, a subquery will return maximum TID for all users since the max is used in together with Group By and than we do an other query to get all the data for those IDs.

Select TID, TData, TUserID, TViewedAt

From Test

Where TID In(

Select Max(TID)

From Test

Group By TUserID

)

Option 2

Slightly more complex to understand, but most likely more efficient, This works on the basis that when t1.TViewedAt is at its maximum value, there is no t2.TViewedAt with a greater value and the t2 rows values will be NULL.

SELECT t1.TID, t1.TData, t1.TUserID, t1.TViewedAt

FROM Test t1

LEFT JOIN Test t2 ON t1.TUserID = t2.TUserID AND t1.TViewedAt < t2.TViewedAt

WHERE t2.TUserID IS NULL;

Result

TID TData TUserID TViewedAt

4 test3 123 2012-10-05 00:00:00.000

5 test2 213 2012-10-03 00:00:00.000