MySQL: remove consecutive duplicate values

Question

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 values:

[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.

Is the last Green in the expected result intentional OR a typo? — Harish Shetty
– Harish Shetty, Commented Feb 23, 2011 at 3:41
I want to remove consecutive duplicates, not just duplicates. So 7-Green is a duplicate of 6-Green, and is therefore dropped. 3-Green stays as is. — Toby Hede
– Toby Hede, Commented Feb 23, 2011 at 3:47

Ike Walker · Accepted Answer · 2011-02-23 01:10:54Z

3

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

answered Feb 23, 2011 at 1:10

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Russell · Accepted Answer · 2011-02-23 05:37:56Z

2

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.

answered Feb 23, 2011 at 5:37

Simon Russell

792 bronze badges

Comments

nate c · Accepted Answer · 2011-02-23 01:33:53Z

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

Collectives™ on Stack Overflow

MySQL: remove consecutive duplicate values

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related