Gary O'Brien wrote:
> 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
>
>
The following works for me
delete from item where rowid not in (
select rowid from item
group by type, container_id
having max(item_id)
)
Regards
Lawrence Chitty
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
