[sqlite] How to remove duplicate records

[Dominique Devienne]

On Wed, Feb 24, 2016 at 1:58 PM, Clemens Ladisch <clemens at ladisch.de> wrote:

> Dominique Devienne wrote:
> >Always ran into Error: only a single result allowed for a SELECT that
> >is part of an expression. --DD
>
> In a join, you can directly use a subquery with multiple columns:
> ... JOIN (SELECT ...) ...
>

Thank you Clemens. --DD

sqlite> create table t (a, b, c);
sqlite> insert into t values (1, 1, 1), (1, 1, 2), (1, 2, 1), (2, 1, 1),
(2, 1, 2), (2, 1, 3);
sqlite> delete from t where rowid in (
   ...>   select t.rowid from t
   ...>     join (select a, b, max(c) as c from t group by a, b) g
   ...>       on t.a=g.a and t.b=g.b
   ...>    where t.c <> g.c
   ...> );
sqlite> select * from t;
1|1|2
1|2|1
2|1|3