sqlite> create table t(name,num,ref);
sqlite> insert into t values('file1',0,'ABCD');
sqlite> insert into t values('file1',1,'ABCD');
sqlite> insert into t values('file1',3,'EF01');
sqlite> insert into t values('file2',0,'BCE2');
sqlite> insert into t values('file2',2,'BCE2');
sqlite> insert into t values('file3',5,'EE34');
sqlite> insert into t values('file4',0,'EE22');
sqlite> insert into t values('file4',1,'FF34');
sqlite> insert into t values('file4',3,'FF34');
sqlite> insert into t values('file4',4,'FF34');
sqlite> select distinct(ref) from t where ref not in (select ref from t
where num=0);
EF01
EE34
FF34-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul Sanderson Sent: Wednesday, January 30, 2013 3:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Thanks All - duplicated means the content is the same as well as the name, different is the filename is the same but the content is different. I need to refine my query to produce only one copy of any that is not in set 0 file1 0 ABCD file1 1 ABCD file1 3 EF01 file2 0 BCE2 file2 2 BCE2 file3 5 EE34 file4 0 EE22 file4 1 FF34 file4 3 FF34 file4 4 FF34 My query would return file1 3 EF01 file3 5 EE34 file4 1 FF34, or file4 3 FF34, or file4 4 FF34 Thanks _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
