Keep group of rows together ordered by min(time) without using group by-CodePudding

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:

They have to be grouped by the session ID so all events from one session appear together
The sessions have to be in order of time (preferably the time of the first event in the session)
The order of the events in the session doesn't actually matter
I don't want to use GROUP BY because I need every event in the table

How would I set about this?

The actual data is here

Retrieved by:

SELECT * FROM dlrcoco_events ORDER BY time DESC`

CodePudding user response：

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:

First column will contain the time of first event in the corresponding session.
It is possible to have two sessions with same min time in which case you must break the tie by having session as second column. This ensures rows belonging to same session remain together.
Finally for each session you sort by event time.

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

CodePudding user response：

You can achieve this by using the following statement:

SELECT * 
FROM dlrcoco_events 
ORDER BY session, time;

Here is a link to an explanation for using ORDER BY: https://www.w3schools.com/sql/sql_orderby.asp

Just a side note: I wouldn't recommend using keywords like "time" as column names. Instead, you could've named it session_time for example.