CODEWITHSUNDEEP
mysql
natsuapo
natsuapo

Reputation: 613

How to get temporal sequence by mysql

In my table there is an id column, a date column and a status column like this: 

ID    DATE    STATUS
1     0106      A
1     0107      A
1     0112      A
1     0130      B
1     0201      A
2     0102      C
2     0107      C

I want to get a temporal sequence of each ID. Which means if in the neighboring time one id is in the same status, then the former ones will be omitted. The query result is like:

ID    DATE    STATUS
1     0112      A
1     0130      B
1     0201      A
2     0107      C

How can I realize it by MySQL?

Upvotes: 0

Views: 66

Answers (1)

Blank
Blank

Reputation: 12378

You have to use variable to do this:

select `id`, `date`, `status`
from (
    select *, @rowno:=if(@grp = `STATUS`, @rowno + 1 , 1) as rowno, @grp := `STATUS`
    from yourtable
    cross join (select @grp := null, @rowno := 0) t
    order by `id`, `date` desc
) t1
where rowno = 1
order by `id`, `date`

SqlFiddle Demo

Upvotes: 1

Related Questions