I'm not sure what do you want to return for the case like this: s1 r1 r2 t1 s1 r1 r2 t2 s1 r1 r3 t2
But for your initial request the following query will be good: select t1.* from table_name t1, (select s, count(*) cnt from (select distinct s, r1, r2 from table_name) group by s) t2 where t1.s = t2.s and t2.cnt = 1; I believe the only index that'll help you is on (s, r1, r2). Pavel On Wed, Jul 28, 2010 at 4:15 PM, Peng Yu <pengyu...@gmail.com> wrote: > Suppose that I have a table of 4 columns. > > S R1 R2 T > -------------------------------- > s1 r1 r2 t1 > s1 r1 r2 t2 > s2 r3 r4 t5 > s2 r5 r4 t6 > s3 r6 r7 t7 > s3 r6 r8 t9 > s4 r9 r10 t10 > > I want to select only the rows where if S column are the same, R1 > column is the same and R2 column is the same. > > For the above examples, I want to keep only the following rows, > because for 's3', 'r7'!='r8' and for 's2', 'r3'!='r5'. Could you show > me what is the correct query to do this? What index I should create on > this table to speed up this query? > > s1 r1 r2 t1 > s1 r1 r2 t2 > s4 r9 r10 t10 > > -- > Regards, > Peng > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
