If your table doesn't have a primary key, this look like your table aren't normalized, maybe you can try broke this table in 2 tables like:
Your definition: CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER); indexes : index1( name ), index2( id2 ), index3( name2 ); Will be: CREATE TABLE t_names(id INTEGER PRIMARY KEY, name TEXT); CREATE INDEX t_name_idx_001 ON (name, id); CREATE TABLE t_relation(id1 INTEGER REFERENCES t_name(id), id2 INTEGER REFERENCES t_name(id), year INTEGER); CREATE INDEX t_relation_idx_001 ON (year, id1, id2); CREATE INDEX t_relation_idx_002 ON (id1, id2); Where t_relation make the relationship between the 'names' (t_name). And the select: SELECT id, name, id2, name2, max(year) y FROM table1 GROUP BY id, name2 ORDER BY name2, y DESC LIMIT 0, 15 Will be: SELECT t2.* , t3.* , MAX(t1.year) AS y FROM t_relation AS t1 JOIN t_name AS t2 ON (t2.id = t1.id1) JOIN t_name AS t3 ON (t3.id = t1.id1) GROUP BY t2.id, t3.name ORDER BY t3.name, y DESC LIMIT 0, 15 And: SELECT id, name, id2, name2 FROM table1 GROUP BY id ORDER BY name2, year DESC LIMIT 0,15 Will be: SELECT t2.* , t3.* FROM t_relation AS t1 JOIN t_name AS t2 ON (t2.id = t1.id1) JOIN t_name AS t3 ON (t3.id = t1.id1) GROUP BY t2.id ORDER BY t3.name, t1.year DESC LIMIT 0, 15 to export the existing data to the new tables you can do: INSERT OR REPLACE INTO t_name SELECT DISTINCT * FROM (SELECT id, name FROM table1 UNION ALL SELECT id2, name2 FROM table1); INSERT OR REPLACE INTO t_relation SELECT id, id2, year FROM table1; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
