Get three last movements of the per ID with LAG and GROUP BY in MySQL I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements.
So I tried grouping by the id and used MAX(date), and used LAG to get the three last movements, but I'm getting an error from (PARTITION BY id ORDER BY id..).
If I delete the two LAG functions, then the query runs. I don't know if I'm missing something there. I'm using MySQL Workbench 8.0 C.E (edit. I'm probably using another version of MySQL 5.7)
SELECT
    id,
    MAX(lastdate),
    LAG(Move, 2, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move1,
    LAG(Move, 1, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move2,
    Move AS Move3 ,
    action_ticket
FROM table 
GROUP BY id

This is the table that I have:




id
lastdate
move
action ticket




12
25/02/20
up
scale


12
26/02/20
down
scale


12
27/02/20
left
solved


15
23/02/20
left
scale


15
22/02/20
up
scale


15
25/02/20
right
solved




And the table that I want to get is:




id
lastdate
move1
move2
move3
action ticket




12
27/02/20
up
down
left
solved


15
25/02/20
up
left
right
solved




Any help is really appreciated. Thanks a lot
Maria J.
Edit. Thank you very much for all the help !
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn
              FROM test )
SELECT id,
       MAX(`date`) `date`,
       MAX(CASE WHEN rn = 3 THEN move END) move1,
       MAX(CASE WHEN rn = 2 THEN move END) move2,
       MAX(CASE WHEN rn = 1 THEN move END) move3,
       MAX(CASE WHEN rn = 1 THEN action_ticket END) action_ticket
FROM cte
GROUP BY id;

fiddle