On Wed, Feb 24, 2016 at 8:17 AM, <admin at shuling.net> wrote: > Now I want to eliminate duplicate record so for two records whose F1 and F2 > values are identical, even if their F3 values are different, then one > record > with the largest value of F3 will be kept and the other one will be > removed. > > Is that possible with the DELETE statement? >
I'm no expert, but with a little thought, here's what I came up with. I tried using a CTE to avoid the temp table, but didn't succeed... Always ran into Error: only a single result allowed for a SELECT that is part of an expression. --DD C:\Users\ddevienne>sqlite3 SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. 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> .header on sqlite> select * from t; a|b|c 1|1|1 1|1|2 1|2|1 2|1|1 2|1|2 2|1|3 sqlite> select a, b, max(c) from t group by a, b; a|b|max(c) 1|1|2 1|2|1 2|1|3 sqlite> create temporary table g as select a, b, max(c) as c from t group by a, b; sqlite> select * from g; a|b|c 1|1|2 1|2|1 2|1|3 sqlite> delete from t where rowid in ( ...> select t.rowid from t join g on t.a=g.a and t.b=g.b where t.c <> g.c ...> ); sqlite> select * from t; a|b|c 1|1|2 1|2|1 2|1|3 sqlite> drop table temp.g;
