Greetings, I'd like to know if anyone has an elegant solution to the problem stated below. I know it could be brute forced but it seems that there should be an elegant SQL solution to this problem.
Given the following table and data, I'd like to remove all items with duplicate types within a container, keeping the most recent (largest item_id) item of that type within each container. Note that the column other_data is included only as a reminder that the rows aren't necessarily identical. Any help would be appreciated. Regards, Gary O'Brien DROP TABLE IF EXISTS item; CREATE TABLE item ( item_id INTEGER PRIMARY KEY, type INTEGER NOT NULL DEFAULT 0, other_data INTEGER NOT NULL DEFAULT 0, container_id INTEGER NOT NULL ); INSERT INTO item (type, container_id) VALUES (0, 1); INSERT INTO item (type, container_id) VALUES (1, 1); INSERT INTO item (type, container_id) VALUES (0, 2); INSERT INTO item (type, container_id) VALUES (1, 2); INSERT INTO item (type, container_id) VALUES (0, 3); INSERT INTO item (type, container_id) VALUES (1, 3); INSERT INTO item (type, container_id) VALUES (0, 1); INSERT INTO item (type, container_id) VALUES (1, 1); INSERT INTO item (type, container_id) VALUES (0, 2); INSERT INTO item (type, container_id) VALUES (1, 2); INSERT INTO item (type, container_id) VALUES (0, 1); INSERT INTO item (type, container_id) VALUES (1, 1); Before the delete operation: SELECT item_id, type, container_id FROM item; 1|0|1 2|1|1 3|0|2 4|1|2 5|0|3 6|1|3 7|0|1 8|1|1 9|0|2 10|1|2 11|0|1 12|1|1 After the delete operation: SELECT item_id, type, container_id from item; 5|0|3 6|1|3 9|0|2 10|1|2 11|0|1 12|1|1 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
