Giorgi Giorgi - 14 days ago 7
SQL Question

MySQL Group by ID and Latest Datetime

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

Answer

I think that with the below you can achieve what you need

 Select TID, TData, TUserID, TViewedAt
 From Test 
 Where TID In(
    Select Max(TID)
    From Test
    Group By TUserID
)

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