I have a table about conversations and different actions for each of them.

How can I select the last action of each conversation and keep it in a new column? I am only interested in actions “message” and “close”, the others can be ignored. So the expected outcome will look like this:

A few details that I was struggling to include in my code:

• when conv_id is 2, if the last action is ‘message’, so the state is ‘open’, even though before that there was a ‘close’.

• when conv_id is 3, after ‘close’, there is some other action that I am not interested in, so the state takes the previous value.

here is the code to get the table:

-- creating a table
CREATE TABLE conversations (
  conv_id INTEGER NOT NULL,
  action_id INTEGER PRIMARY KEY,
  action TEXT NOT NULL,
  date DATETIME NOT NULL
);
-- inserting some values
INSERT INTO conversations VALUES (1,11,'message','10:00');
INSERT INTO conversations VALUES (1,12,'message','10:05');
INSERT INTO conversations VALUES (1,13,'close','10:10');
INSERT INTO conversations VALUES (2,21,'message','10:00');
INSERT INTO conversations VALUES (2,22,'message','10:05');
INSERT INTO conversations VALUES (2,23,'close','10:10');
INSERT INTO conversations VALUES (2,24,'message','10:15');
INSERT INTO conversations VALUES (3,31,'message','10:00');
INSERT INTO conversations VALUES (3,32,'message','10:05');
INSERT INTO conversations VALUES (3,33,'close','10:10');
INSERT INTO conversations VALUES (3,34,'something else','10:15');

Thank you!

CodePudding user response：

USING LAST_VALUE window function,

SELECT *, 
       CASE LAST_VALUE(IF(action IN ('message', 'close'), action, NULL) IGNORE NULLS) OVER w
         WHEN 'close' THEN 'closed'
         WHEN 'message' THEN 'open'
       END AS state  
  FROM conversations
WINDOW w AS (PARTITION BY conv_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
 ORDER BY conv_id, date;

Query results: