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;
Comments
Post a Comment