"Lawrence Chitty" <lawrence.chi...@ntlworld.com> wrote in message news:49a32a32.6040...@ntlworld.com > Gary O'Brien wrote: >> 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. >> > 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) > )
This only works by accident. First, HAVING clause is a no-op: it says select only those groups that have max(item_id) != 0, which is all of them. Second, when you have a field in a GROUP BY statement not bound by an aggregate function, SQLite would select an arbitrary row from the group to get the value from - not necessarily the row on which, say, maximum is achieved in some invocation of max() in the same statement. It just so happens that in this case, SQLite chooses the largest rowid in each group. A similar statement should work though: delete from item where item_id not in ( select max(item_id) from item group by type, container_id ); Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users