scottlabs scottlabs - 4 months ago 4x
MySQL Question

MySQL query ordering data two ways

I am tracking page views within modules using the following table:

id | page_id | user_id | session_id | created

Every page is unique to a certain module; so page 14 only exists in module 2, for instance.

I am trying to write a query that tells me when a viewing session began (GROUP BY session_id) for a particular module, but I am having trouble with my ordering. Basically, for that grouped session, I want to return the earliest 'created' (the starting time of the viewing session) and the latest page_id (how far they got within the module).

What is the best way to accomplish this?


I think you'll need to utilise sub-queries in the select:

SELECT a.session_id, MIN(a.created) as start_time, 
b.page_id AS last_page, b.end_time
FROM table a 
   (SELECT b.session_id, MAX(b.created) as end_time, MAX(b.page_id) 
   FROM table b GROUP BY b.session_id) 
ON a.session_id = b.session_id GROUP BY a.session_id