MySQL Group by ID and Latest Datetime

Question

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

Solution 1

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

Solution 2

You need to GROUP BY TUserID for the max aggregate.

SELECT
  TID,
  TData,
  main.TUserID
  main.TViewedAt
FROM
  yourTable main
  JOIN ( 
    SELECT TUserID, MAX(TViewedAt) AS TViewedAT FROM yourTable GROUP BY TUserID
  ) tmax ON main.TUserID = tmax.TUserID AND main.TViewedAt = tmax.TViewedAt

This is a portable method which joins against a subquery. The subquery pulls the latest TUserID,TViewedAt per user, and that is joined back against the main table to pull the TData that matches.

If you only want one user at a time, you can do:

SELECT 
  TData,
  TViewedAt
FROM yourTable
WHERE TUserID = 'someid'
GROUP BY TUserId
HAVING TViewedAt = MAX(TViewedAt)

The above will only work in MySQL the way I have written it though. Other RDBMS will complain about the fact that TData appears in the SELECT list but is not in the GROUP BY.

Solution 3

Following query, may help you-

select * from pages where date(create_date)=(select date(create_date) from pages order by create_date limit 1)