I have this table to be transformed using SQL:
| ID | message | time |
|---|---|---|
| value_C | fgh | 2022-08-16 21:00:00 |
| value_B | ksl | 2022-08-16 21:00:00 |
| value_A | cde | 2022-08-16 21:00:00 |
| MODE | B | 2022-08-16 21:00:00 |
| value_C | fgh | 2022-08-16 20:00:00 |
| value_B | fgh | 2022-08-16 20:00:00 |
| value_A | cde | 2022-08-16 20:00:00 |
| MODE | B | 2022-08-16 20:00:00 |
| value_C | xyz | 2022-08-16 19:00:00 |
| value_B | xyz | 2022-08-16 19:00:00 |
| value_A | abc | 2022-08-16 19:00:00 |
| MODE | A | 2022-08-16 19:00:00 |
wanted to do some transformation so it becomes this:
| ID | message | time |
|---|---|---|
| value_B | ksl | 2022-08-16 21:00:00 |
| MODE | B | 2022-08-16 21:00:00 |
| value_B | fgh | 2022-08-16 20:00:00 |
| MODE | B | 2022-08-16 20:00:00 |
| value_A | abc | 2022-08-16 19:00:00 |
| MODE | A | 2022-08-16 19:00:00 |
However, I couldn't think of a good logic to do this. Any help would be very much appreciated.
CodePudding user response：
On MySQL 8 , we can use ROW_NUMBER() here:
WITH cte AS (
SELECT *, MAX(CASE WHEN ID = 'MODE' THEN message END) OVER
(PARTITION BY time) mode_msg
FROM yourTable
)
SELECT ID, message, time
FROM cte
WHERE ID = 'MODE' OR ID LIKE CONCAT('%_', mode_msg)
ORDER BY time DESC, IF(ID = 'Mode', 1, 0);
Demo
We use pivoting logic to line up the mode letter value with each record from a given timestamp group. The logic for retaining a record then is either that the ID matches the mode, or the ID be the mode record itself.