How to select rows from MySQL based on max value of a column + grouping

Question

I've got a table that contains (let's say) all the times when a user looked at a specific webpage. Users can of course look at a page more than once, so there can be multiple entries for users and pages, like so:

nid     time    user  page_id
 25     8000       4      467
 24     7000       1      482
 23     6000       1      484
 22     5000       1      482
 21     4000       5      467
 20     3000       4      467

I want to do a query that returns the rows corresponding to every page viewed by every user WITH THE CATCH THAT if a user looked at a page more than once, I get the row corresponding to the most recent view (i.e., the largest value of TIME). Thus, I should get this:

nid     time    user  page_id
 25     8000       4      467
 24     7000       1      482
 23     6000       1      484
 21     4000       5      467

We lose row 22 because user 1 looked at page 482 at a later time, and we lose row 20 because user 4 looked at page 467 at a later time.

I almost have this figured out, but I can't quite crack it, while also convincing myself that the results I'm getting will be generally correct and not just an accident of my test cases. I keep going back and forth between GROUP BY or DISTINCT queries and embedded queries, and then my brain explodes. Any suggestions? Thanks!

Solution 1

If you need the full row you can use this:

SELECT fullTable.nid as nid, 
       recent.time as time, 
       fullTable.user as user, 
       fullTable.page_id as page_id 
  FROM TableName fullTable 
         INNER JOIN   (SELECT MAX(t1.time) as time, t1.user, t1.page_id 
                         FROM TableName t1 
                     GROUP BY user, page_id) recent
                 ON recent.time = fullTable.time AND 
                    recent.user = fullTable.user AND 
                    recent.page_id = fullTable.page_id
ORDER BY time DESC

If you ask for a column outside the "group by" clause, mysql can return any value for this column inside this group. So if all the values inside the group are not the same, that is your case, you can't include it directly on the select clause, you need to use a join.

You can read more about not grouped columns on MySQL on the reference

If you don't need the nid field, you can use this other:

SELECT MAX(time) as time, user, page_id 
  FROM TableName
GROUP BY user, page_id
ORDER BY time DESC

Solution 2

Try this:

SELECT *
  FROM <YOUR_TABLE>
 WHERE (user, page_id, time) IN
    (
    SELECT  user, page_id, MAX(time) time
      FROM <YOUR_TABLE>
    GROUP BY user, page_id
   )