mysqli - Filter out closest dublicated rows (but not completely all) from MySQL table -

in table need filter out nearest dublicated rows have same status_id (but not all) when user_id same. group by or distinct did not in situation. here example:

--------------------------------------------------- | id  |  user_id | status_id | date               | --------------------------------------------------- |  1  |    10    |    1      | 2010-10-10 10:00:10| |  2  |    10    |    1      | 2010-10-11 10:00:10| |  3  |    10    |    1      | 2010-10-12 10:00:10| |  4  |    10    |    2      | 2010-10-13 10:00:10| |  5  |    10    |    4      | 2010-10-14 10:00:10| |  6  |    10    |    4      | 2010-10-15 10:00:10| |  7  |    10    |    2      | 2010-10-16 10:00:10| |  8  |    10    |    2      | 2010-10-17 10:00:10| |  9  |    10    |    1      | 2010-10-18 10:00:10| |  10 |    10    |    1      | 2010-10-19 10:00:10| 

have like:

--------------------------------------------------- | id  |  user_id | status_id | date               | --------------------------------------------------- |  1  |    10    |    1      | 2010-10-10 10:00:10| |  4  |    10    |    2      | 2010-10-13 10:00:10| |  5  |    10    |    4      | 2010-10-14 10:00:10| |  7  |    10    |    2      | 2010-10-16 10:00:10| |  9  |    10    |    1      | 2010-10-18 10:00:10| 

oldest entries (by date) should remain in table

you want keep each row previous status different, based on id or date column.

if ids sequential (as in question), can convenient join:

select t.* t left outer join      t tprev      on t.id = tprev.id+1 tprev.id null or tprev.status <> t.status; 

if ids not sequential, can previous 1 using correlated subquery:

select t.* (select t.*,              (select t2.status               t t2               t2.user_id = t.user_id ,                     t2.id < t.id               order t2.id desc               limit 1              ) prevstatus       t      ) t prevstatus null or prevstatus <> t.status;