MySQL group by ID and latest date

Question

I saw similar questions here, but none of them helped me.

I have a table allowing to say Test (using mysql MyISAM)

The test has the following scheme:

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 every time the user visits the test page at present, she adds entries to this table, recording the user ID, whoever visits, some data and the date and time the user visits, when the TID automatically increases each time.

Then I have a backend management page where I can see which user visited the test page, how many times, data and when. This is basically a table with a list of the last 20-25 rows. My query should select only one record per data. The data may be the same for the user, but at different times, but I only want to select one of the data for each user, which is the most recent. Thus, if a user visits the test page 10 times, 10 records are sent to the database, but only 1 last record in the table is displayed. If another user visits the page 15 times, he displays 1 record for this second user, which is the last, so in general we now have 2 rows displayed in 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 request:

  SELECT * FROM Test WHERE TUserID = 123 GROUP BY TData ORDER BY TViewedAt 

returns 2 rows, of which none of these lines is the last.

thanks alot

Answer 1

I think that with the help below you can achieve what you need. These operations are called the "Maximum Group".

Option 1

This is easiest to understand, the subquery will return the maximum TID for all users, since max used together with Group By , and we make another request to get all the data for these identifiers.

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

Option 2

Slightly more difficult to understand, but most likely more effective. This works on the basis that when t1.TViewedAt has a maximum value, there is no t2.TViewedAt with a large value, and the values ​​of the t2 strings 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 

Answer 2

For maximum aggregation you need GROUP BY TUserID .

 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 that connects to a subquery. The subquery pulls out the last TUserID,TViewedAt for each user, and joins back with the main table to pull up the TData that matches.

If you only need 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, as I wrote it. Other RDBMSs will complain that TData appears in the SELECT list but is not in GROUP BY .

Answer 3

The following query may help you -

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

Answer 4

 SELECT t1.TData, t1.TUserID, t1.TViewedAt FROM Test t1 JOIN (SELECT TUserID, MAX(TViewedAt) FROM Test GROUP BY TUserID) t2 ON t1.TUserID = t2.TUserID AND t1.TViewedAt=t2.TViewedAt