MySQL: remove consecutive duplicate values

I have a MySQL table that returns a list of values that contains consecutive duplicates (when ordered by a timestamp).

For example, when querying, I need to only return the consecutively duplicated val开发者_开发技巧ues:

[1, "Yellow"]
[2, "Yellow"]
[3, "Green"]
[5, "Black"]
[6, "Green"]
[7, "Green"]

The numbers here are being used for reference - the value is actually the string "Green", so for the above case the new unduped list would be:

[1, "Yellow"]
[3, "Green"]
[5, "Black"]
[6, "Green"]

Is there a smart way of handling this problem with MySQL?

Using Rails/ActiveRecord, not that that should make a difference, but I can do this no problems by manipulating an Array, just wondering if there is a smarter way of handling this.

One way to solve a problem like this is to use a sub-query with a user variable. You can track the color value from the previous row using the user variable, and then use the user variable in the where clause of the outer query to filter the rows you return.

Try something like this:

select id,this_color as color
from
(
select id,@last as last_color,@last:=color as this_color
from your_table
order by id
) as sub
where this_color != last_color

Building on Ike Walker's answer, which is possibly a bit more complex than it needs to be:

set @last='';
select id,@last as last_color,@last:=color as this_color
from your_table
having this_color != last_color;

HAVING lets you use the computed columns. Setting @last means it won't remember the value from the last query you ran, which might give you strange results.

Selecting the distinct rows if pretty easy. There is a little more work to actually deleting not the distinct rows you select. The syntax in a delete is a lot more finicky than a select. You have to formally declare another table and join against (it won't let you make correlated subquery in the where clause.)

Select the the ids you want to remove in subquery, then join against it in the delete statement:

delete from test
    using test,
    (
        -- the rows i do not want
        select id
        from test
        where id not in
            -- select the rows i do want (will use the minimum id instead of being arbitrary)
            (select min(id) as id from test group by color)
    ) as temp
    where test.id = temp.id
;

These are the rows the subquery selects:

id      color
2       yellow
6       green
7       green

Final rows after delete:

id      color
1       yellow
3       green
5       black