Geek Answers Handbook

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

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 type to get a result similar to the following

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

As you can see, the first two lines were grouped because there is no other type between them, for example, what you see in the call logs of mobile phones.

How can I achieve this in SQLite?

+6
source share
2 answers

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>
+1
source

NOT EXISTS Name Type ( ). , .

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: http://sqlfiddle.com/#!5/0700cb/3

+3

More articles:


All Articles