CODEWITHSUNDEEP
mysqlselectwhere-clause
Happy Mittal
Happy Mittal

Reputation: 3747

select rows based on next row in mysql

I have a table like this : 

Type | Time
1    | 234234
2    | 234235
1    | 234238
3    | 234239
4    | 234240
1    | 234242
2    | 234245

I want to count number of all those rows where type=1 and next row's type=2.
For ex : The result here is 2. I don't know how to put where clause on next row.

Upvotes: 0

Views: 218

Answers (1)

Taryn
Taryn

Reputation: 247860

You should be able to implement user defined variables to get the total:

select count(*) Total
from
(
  select type, 
    @row:=(case when @prev=1 and type=2 then 'Y' else 'N' end) as Seq,
    @prev:=type 
  from yourtable, (SELECT @row:=null, @prev:=null) r
  order by time, type
) src
where Seq = 'Y'

See SQL Fiddle with Demo

Upvotes: 1

Related Questions