CODEWITHSUNDEEP
mysql
syaz
syaz

Reputation: 2679

MySQL: SELECT if next row is not as what is expected

So I have a log table. Rows are basically like this:

id  function
n   init()
n+1 check()
n+2 done()
... repeat

But there are problems when it's like this:

id  function
n   init()
n+1 check()
n+2 done()
n+3 check() <-- should be init!
n+4 done()

How do I find rows that happen only in init->check->done->check->done contagious order? Basically after row done() it must be check() again in order to be SELECTed.

Is there a way to do this in 1 (or minimal) query? Performance is not an issue.

Upvotes: 1

Views: 193

Answers (2)

Julian Aubourg
Julian Aubourg

Reputation: 11436

 SELECT line2.*
   FROM log line2
        JOIN log line1
        ON line2.id = line1.id + 1
  WHERE line1.function = 'done()'
    AND line2.function = 'check()'

I know you said performance wasn't an issue, but still ;)

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332731

Use:

SELECT l.*
   FROM LOG l
  WHERE l.function = 'check()'
      AND EXISTS(SELECT NULL
                           FROM LOG t
                          WHERE t.function = 'done()'
                               AND t.id = l.id-1)

...to get the check() row which comes after a done(), and apparenly should be "init()" instead.

Upvotes: 1

Related Questions