Select newest record matching session_id

Question

I should have paid more attention in relational databases class, so I need some help.

I have a table structured like so (there's more, but I'm only posting relevant info):

+------------------+-------------+------+-----+-------------------+-------+
| Field            | Type        | Null | Key | Default           | Extra |
+------------------+-------------+------+-----+-------------------+-------+
| session_id       | char(32)    | NO   |     | NULL              |       |
| file             | varchar(30) | NO   |     | NULL              |       |
| access_time      | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
+------------------+-------------+------+-----+-------------------+-------+`

I want to select the file field of the record with the greatest access_time for each session_id. So pretty much the last page visited in each session.

I've tried countless combinations of DISTINCT and the GROUP BY clause, but I can't seem to wrap my head around it for some reason. I'm using MySQL. Help please.

Answer 1

I know this will work in SQL Server, and it should port to MySQL.

select session_id, timestamp, file 
from table t1
join (select session_id, max(timestamp) as timestamp) 
        from table group by session_id) as t2
   on t1.session_id = t2.session_id
     and t1.timestamp = t2.timestamp

Answer 2

select * from my_table t1
    where t1.access_time = (
       select max(t2.access_time) 
         from my_table t2 
         where t2.session_id = t1.session_id
       )

Answer 3

SELECT
     T1.session_id,
     T1.file,
     T1.access_time
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.session_id = T1.session_id AND
     T2.access_time > T1.access_time
WHERE
     T2.session_id IS NULL

If there are duplicate access_time values for the same session_id then you might get multiple rows for that session_id. You can tweak this to avoid that if needed depending on your business rules for that situation.