0

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 !

6
  • Welcome to DBA.SE. Are you sure the error message is from the statement you are actually running? Because the (PARTITION BY t1.averia ORDER BY t1.averia..) in the error message doesn't match any of the (PARTITION BY id ORDER BY id ASC) in the statement you are using. Please hit edit and provide the full details including the full error message in your question. Thanks.
    – John K. N.
    Commented Jan 27, 2021 at 8:50
  • Yes, I mixed up with the original query, this is the part that I get the error as you say (PARTITION BY id ORDER BY id ASC)
    – Maho Lau
    Commented Jan 27, 2021 at 8:53
  • Hi, and welcome to the forum! You should provide your table structures as DDL and your table data as DML - it reduces the potential for error and the duplication of effort on behalf of those answering - help us to help you! (Aside): Also, you shouldn't use SQL keywords like date for your table and/or column names - it makes debugging difficult, error messages confusing and your SQL non-portable. Even though you're using Workbench 8, you might be using a different version of the server - please always include that in your tags!
    – Vérace
    Commented Jan 27, 2021 at 9:29
  • Thanks for the advice! I will fix for better understanding
    – Maho Lau
    Commented Jan 27, 2021 at 9:52
  • @MahoLau, dbfiddle.uk is a very useful tool to create a setup Commented Jan 27, 2021 at 9:54

3 Answers 3

1
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

2
  • Im getting the error 'cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) rn ' at line 1 like it would be a MySQL ver.5.7 or previous
    – Maho Lau
    Commented Jan 27, 2021 at 9:34
  • @MahoLau, select @@version; Commented Jan 27, 2021 at 9:52
0

Disclaimer, this answer will not provide a solution, only point out some errors in your query. There are several problems with your query:

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

Error 1: action_ticket, Since there is no successor column you should remove the trailing ","

Error 2: FROM table t1 You should use the name of the table, not the word table

FROM test t1

Error 3: Id can't be a candidate key since there are multiple id's in your sample data. The GROUP BY clause must contain enough information to uniquely identify each row

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 test t1
GROUP BY t1.id, Move, action_ticket

Now, this obviously does not meet the requirements for your query. There are several more steps needed to get the required result, but the result won't be as elegant as @Akina 's solution so I'll leave it there.

0

You can do it using window functions only as follows (see the fiddle here):

CREATE TABLE test
(
  id INTEGER NOT NULL,           -- appropriate FK reference here.
  m_date DATE NOT NULL,
  action VARCHAR (25) NOT NULL,  -- appropriate FK reference here.
  ticket VARCHAR (25) NOT NULL,  -- appropriate FK reference here.
  INDEX (m_date),
  INDEX (id)
);

Populate the table:

INSERT INTO test VALUES
(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');

Now for the SQL - I've left an intermediary step in so that the logic can be followed:

SELECT 
  id, 
  LAG(m_date, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS dt,
  LAG(action, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS m_3,
  LAG(action, 1) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS m_2,
  action AS m_1,
  LAG(ticket, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS ticket_status
FROM 
  test
ORDER BY id, m_date DESC;

Result:

id  dt  m_3 m_2 m_1 ticket_status
12              left    
12          left    down    
12  2027-02-20  left    down    up  solved
15              right   
15          right   left    
15  2025-02-20  right   left    up  solved
6 rows

Now, we can see (better viewed on the fiddle) that the records where neither the m_3 nor the m_2 fields are NULL gives us all the data we require.

So, we now put this query in a subselect:

SELECT t.* FROM 
(
  SELECT 
    id, 
    LAG(m_date, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS l_d,
    LAG(action, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS m_3,
    LAG(action, 1) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS m_2,
    action AS m_1,
    LAG(ticket, 2) OVER (PARTITION BY id ORDER BY id ASC, m_date DESC) AS ticket_status
  FROM 
    test 
  ORDER BY id, m_date DESC
) AS t
WHERE m_3 IS NOT NULL AND m_2 IS NOT NULL
ORDER BY id ASC;

and the result as requested:

id  l_d          m_3     m_2    m_1 ticket_status
12  2027-02-20  left    down     up        solved
15  2025-02-20  right   left     up        solved

We can do this because we know that we are only interested in the last 3 movements. If that's the case, then the above works - but it won't work if there are only 1 or 2 records - whereas @Akina's will - again, see the fiddle!

I did a performance analysis and it appears to show that @Akina's solution would be more performant - but testing with a small no. of records is unreliable - I would urge you to test with a realistic dataset before committing to a solution!

2
  • We can do this because we know that we are only interested in the last 3 movements. But we must be sure that there is strictly 3 movements per id.
    – Akina
    Commented Jan 27, 2021 at 10:34
  • I edited my answer to show that this was the case - indeed I had it in the fiddle. I liked the "trick" (I don't mean that in a pejorative sense) with the MAX(CASE... (+1)! I'll also bring it to the OP's attention!
    – Vérace
    Commented Jan 27, 2021 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.