CODEWITHSUNDEEP
mysqlsubquery
Merix
Merix

Reputation: 63

MySQL - How to find rows based on value in previous row

I have a setting table:

create table settings(
    id int
  , date datetime
  , user varchar(20)
  , status varchar(10)
  );

insert into settings values
 (1 , '2020-07-10 14:36:06', 'user1', 'INACTIVE'),
 (1 , '2020-07-10 14:39:54', 'user1', 'ACTIVE'),
 (1 , '2020-07-10 14:44:06', 'user1', 'INACTIVE'),
 (1 , '2020-07-10 14:46:06', 'user2', 'INACTIVE'),
 (1 , '2020-07-10 14:49:06', 'user2', 'SCHEDULED'),
 (1 , '2020-07-10 14:55:06', 'user2', 'ACTIVE'),
 (2 , '2020-07-12 14:36:06', 'user1', 'INACTIVE'),
 (2 , '2020-07-13 14:39:54', 'user1', 'INACTIVE'),
 (2 , '2020-07-13 16:44:06', 'user1', 'ACTIVE'),
 (2 , '2020-07-15 14:46:06', 'user3', 'INACTIVE'),
 (2 , '2020-07-15 15:49:06', 'user3', 'INACTIVE'),
 (2 , '2020-07-17 14:46:06', 'user3', 'ACTIVE')
 ;

example code with insert code is in sql fiddle

The process starts from INACTIVE status and it is scheduled by system then user could change to SCHEDULED or ACTIVE.

I need to find rows which have ACTIVE status but only these one which before is row with INACTIVE status grouped by user. Specific case (not sure if it is possible) to find rows with ACTIVE status but if previous rows have SCHEDULED -> INACTIVE sequence.

I selected on the below screenshot which rows should be returned:

enter image description here

I was trying with it but the result should return previous row under current row.

select * from (  
select @rownum:=@rownum+1 row, a.* 
from settings a
where a.status = 'ACTIVE'
) as cr

LEFT JOIN (SELECT  @rownum2:=@rownum2+1 row, b.* FROM settings b
where b.status = 'INACTIVE' ) as pr
on cr.id = pr.id and cr.date > pr.date

Upvotes: 1

Views: 708

Answers (1)

CaffeinatedCod3r
CaffeinatedCod3r

Reputation: 882

You could use self-join to do this. After doing self-join, from one table (say A) get ACTIVE time for each date and from another table (say B) get Inactive time where B.date<A.date.

Then you can perform other operation on both date like getting time duration or something.

Check fiddle here : http://sqlfiddle.com/#!9/84789e/23

Query :

SELECT A.user,max(B.date) as Inactive,min(A.date) as Active 
FROM settings A JOIN settings B
ON A.id=B.id and A.user=B.user
WHERE A.status = 'ACTIVE'
AND B.status = 'INACTIVE'
AND B.date<A.date
GROUP BY  A.date,A.user

Upvotes: 1

Related Questions