Re: [sqlite] count distinct group by

2010-07-30 Thread Benoit Mortgat
On Thu, Jul 29, 2010 at 00:47, Igor Tandetnik wrote: > Peng Yu wrote: >> 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. > > select * from mytable where s in > (select s from mytable >  group by s >  having min(r1)=max(r1) and min

Re: [sqlite] count distinct group by

2010-07-28 Thread Igor Tandetnik
Peng Yu wrote: > Suppose that I have a table of 4 columns. > > S R1 R2 T > > s1r1 r2 t1 > s1r1 r2 t2 > s2r3 r4 t5 > s2r5 r4 t6 > s3r6

Re: [sqlite] count distinct group by

2010-07-28 Thread Pavel Ivanov
I'm not sure what do you want to return for the case like this: s1r1 r2 t1 s1r1 r2 t2 s1r1 r3 t2 But for your initial request the following query will be good: select t1.* from table_name t1, (select s, count(*) cnt fr

[sqlite] count distinct group by

2010-07-28 Thread Peng Yu
Suppose that I have a table of 4 columns. S R1 R2 T s1r1 r2 t1 s1r1 r2 t2 s2r3 r4 t5 s2r5 r4 t6 s3r6 r7 t7 s3r6