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

SELECT
    id,
    MAX(t1.date),
    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 t1
GROUP BY t1.id

This is the table that I have:

id	date	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	date	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.

Answer

This query should help:

Select * from
(SELECT
    id,
    MAX(t1.date),
    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,
    LAST_VALUE(move) OVER (PARTITION BY id ORDER BY id ASC) AS Move3,
    LAST_VALUE(action_ticket) OVER (PARTITION BY id ORDER BY id ASC) AS ActionTicket
FROM t1
GROUP BY id, move, action_ticket
) as t1
where Move1 is not null

I used a derived table that created all the rows and filtered out the rows where Move1 is null because that row didn’t have enough information.

I’m not sure from the data you provided if all the ids have exactly 3 rows in the table or not. If there are a variety of row counts, this query will need to be revisited.