Hmm thanks Roger Table could have a few million rows, i'll have a play and see what the run time is. The relevant column is indexed
On 20 August 2011 17:14, Roger Andersson <r...@telia.com> wrote: > On 08/20/11 05:42 PM, Paul Sanderson wrote: >> Hi all >> >> I am trying to create a query that works to craete a subset of a table >> based on duplicate items >> >> Examples work best so consider the contrived table with the following rows >> 10 socata >> 7 socata >> 13 cessna >> 2 piper >> 7 piper >> 55 piper >> 1 diamond >> >> I want to see the subset that is >> 10 socata >> 7 socata >> 2 piper >> 7 piper >> 55 piper >> >> i.e. all rows that have a matching value in any other row in the second >> column >> >> any ideas? >> _______________________________________________ > Might be more efficient queries if there is a LOT of records but this > seems to do the trick. > create table tbl (id,text); > insert into tbl values (10, 'socata'); > insert into tbl values (7, 'socata'); > insert into tbl values (13, 'cessna'); > insert into tbl values (2, 'piper'); > insert into tbl values (7, 'piper'); > insert into tbl values (55,'piper'); > insert into tbl values (1, 'diamond'); > select * from tbl where text in (select text from tbl group by text > having count(*) > 1); > 10|socata > 7|socata > 2|piper > 7|piper > 55|piper > > Cheers Roger > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Paul Sanderson Sanderson Forensics +44 (0)1326 572786 www.sandersonforensics.com http://www.twitter.com/sandersonforens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
