I have a table that looks like this:
| user_id | datetime | activity |
|---|---|---|
| 2 | 2022-01-10 12:00:00 | Logout |
| 1 | 2022-01-09 12:00:00 | Login |
| 3 | 2022-01-08 12:00:00 | Login |
| 3 | 2022-01-07 12:00:00 | Register |
| 2 | 2022-01-06 12:00:00 | Login |
| 1 | 2022-01-05 12:00:00 | Register |
If I query the table sorted by datetime DESC I will get the result like the above.
How can I extend the query so that I can get the results grouped by the user_id like below?
| user_id | datetime | activity |
|---|---|---|
| 2 | 2022-01-10 12:00:00 | Logout |
| 2 | 2022-01-06 12:00:00 | Login |
| 1 | 2022-01-09 12:00:00 | Login |
| 1 | 2022-01-05 12:00:00 | Register |
| 3 | 2022-01-08 12:00:00 | Login |
| 3 | 2022-01-07 12:00:00 | Register |
The logic is the records will be sorted by datetime DESC at first and when it encounters the user_id for the record, it will aggregate all records belonging to the user_id together and maintaining the datetime DESC sorting within the user_id group.
CodePudding user response：
Use MAX() window function in the ORDER BY clause:
SELECT *
FROM tablename
ORDER BY MAX(datetime) OVER (PARTITION BY user_id) DESC,
user_id, -- just in case there are duplicate datetimes, remove this if the column datetime is unique
datetime DESC;
See the demo.