Berty Berty - 3 years ago 64
SQL Question

How to eliminate only continuous duplicates but not all duplicates in a select query (MySQL)?

I have a table like this:

01-Jul-17      100

02-Jul-17      100

03-Jul-17      300

04-Jul-17      300

05-Jul-17      500

06-Jul-17      500

07-Jul-17      300

08-Jul-17      400

09-Jul-17      100

10-Jul-17      100

What I want to output is (in this order) by eliminating the continuous duplicates but not all duplicates:







I cannot select Distinct, as it will eliminate the second instances of 300, 100. Is there a way to achieve this result in MySQL?

Answer Source

You want to get the previous value. If the dates really have no gaps or duplicates, just do:

select t.*
from t left join
     t tprev
     on t.col1 = date_add(tprev.col1, interval 1 day)
where tprev.col2 is null or tprev.col2 <> t.col2;


If the dates don't meet these conditions, then you can use variables:

select t.*
from (select t.*,
             (@rn := if(@v = col2, @rn + 1,
                        if(@v := col2, 1, 1)
             ) as rn
      from t cross join
           (select @v := 0, @rn := 0) params
      order by t.col1
     ) t
where rn = 1;

Note that MySQL does not guarantee the order of evaluation of expressions in the SELECT. So variables should not be assigned in one expression and then used in another -- they should be assigned in a single expression.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download