"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
