CODEWITHSUNDEEP
androidsqlite
Ahmed Galal
Ahmed Galal

Reputation: 1228

How to remove consecutive rows with duplicate data in certain fields (in SQLite)?

Let's say for example you have a table like this:

-------------------------------
Date        Name          Type
2017-01-01  John          1
2017-01-02  John          1
2017-01-03  Mike          2
2017-01-04  John          1
-------------------------------

I want to be able to group by the type to get a result like the following

-------------------------
Name          Type
John          1
Mike          2
John          1
-------------------------

As you can see the first two rows was grouped because there is no different type in between them, like the thing you see in mobile phones call logs.

How can I achieve that in SQLite?

Upvotes: 6

Views: 1040

Answers (2)

Doug Currie
Doug Currie

Reputation: 41180

You can do this with an ordered temp table and some rowid comparisons:

sqlite> create table t (date, name, type);
sqlite> insert into t (date, name, type) values ( '2017-01-01', 'John', 1);
sqlite> insert into t (date, name, type) values ( '2017-01-02', 'John', 1);
sqlite> insert into t (date, name, type) values ( '2017-01-03', 'Mike', 2);
sqlite> insert into t (date, name, type) values ( '2017-01-04', 'John', 1);

sqlite> create temp table tp as select date, name, type from t order by date;
sqlite> delete from tp
     where tp.name = (select name from t where t.rowid = tp.rowid - 1)
     and tp.type = (select type from t where t.rowid = tp.rowid - 1);
sqlite> select * from tp;
2017-01-01|John|1
2017-01-03|Mike|2
2017-01-04|John|1
sqlite>

Upvotes: 1

Steve Chambers
Steve Chambers

Reputation: 39424

The following query achieves this by a NOT EXISTS for the same Name and Type values on the previous row (if any). The previous row is found by looking for the maximum date that is strictly less than the date of the current row.

SELECT *
FROM tbl t1
WHERE NOT EXISTS (
  SELECT *
  FROM tbl t2
  WHERE t2.Name = t1.Name
  AND t2.Type = t1.Type
  AND t2.`Date` = (SELECT MAX(`Date`)
                   FROM tbl t3
                   WHERE t3.`Date` < t1.`Date`)
);

SQL Fiddle demo: http://sqlfiddle.com/#!5/0700cb/3

Upvotes: 3

Related Questions