natsuapo natsuapo - 4 months ago 17
SQL Question

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?

JPG JPG
Answer

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