I have a table of events generated by users doing stuff. It looks roughly like this:
session | time | event | etc.
06efaa1805ef670c | 2022-01-07 08:41:03 | lockFound | etc.
06efaa1805ef670c | 2022-01-07 08:41:43 | lockLost | etc.
154032b09bec72f8 | 2022-01-07 10:41:03 | lockFound | etc.
154032b09bec72f8 | 2022-01-07 10:41:43 | lockLost | etc.
I need to get all of these events in a certain order:
How would I set about this?
The actual data is here
Retrieved by:
SELECT * FROM dlrcoco_events ORDER BY time DESC`
If using MySQL 8 or later then you can use window functions:
SELECT *
FROM dlrcoco_events
ORDER BY MIN(time) OVER (PARTITION BY session), session, time
Explanation of order by:
session
as second column. This ensures rows belonging to same session remain together.The window function could be replaced with a correlated sub query:
ORDER BY (
SELECT MIN(time)
FROM dlrcoco_events AS x
WHERE x.session = dlrcoco_events.session
), -- other columns as described above