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
