I have a table like this:
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?
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.