Question 20652 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I find adjacent rows in mysql?

I have a log table that records user events. These are ordered by a timestamp.

id event user context timecreated
5 login 234 0 65535
8 open 234 5 65536
13 open 234 9 65539
17 open 234 8 65512
43 login 128 0 65513
44 login 293 0 65515
65 open 293 5 65516
66 open 234 5 65519
97 login 567 0 65521
99 open 567 9 65528

I want a query that finds the first event='open' rows that are adjacent-next to event='login' rows for the same user id (and other possible filters

psuedo-example:

select * from log where event='open' and context=5 and timecreated > (select timecreated from log where event='login' and user = (the same as the previous query) limit 1).

In the table above this would match the rows

id event user context timecreated
8 open 234 5 65536
65 open 293 5 65516
99 open 567 9 65528

Is this possible in a MySQL statement?

Tags:

2

Answers


  1. 0 
    select * from test t
where t.event = 'open' and
    t.user =
   (select user
    from test
    where id = (select max(id) from test where  id < t.id) and event = 'login');
    Login or Signup to reply.
  2. 0

    Try this using the LAG() window function:

    SELECT *
FROM (
    SELECT *, LAG(event) OVER (PARTITION BY user ORDER BY timecreated) AS prev_event
    FROM log
) t
WHERE event = 'open'
AND prev_event = 'login'
AND context = 5

    If you are using MySQL < 8, you could use:

    SELECT o.*
FROM log o
JOIN log l
    ON l.event = 'login'
    AND o.user = l.user
    AND o.timecreated > l.timecreated
WHERE o.event = 'open'
AND o.context = 5
AND NOT EXISTS (
    SELECT 1 FROM log
    WHERE user = o.user
    AND timecreated > l.timecreated
    AND timecreated < o.timecreated
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search