> I wasn't sure SQLite supported count(distinct). The docs don't seem to > mention it. But yes, apparently it does and the statement can be simplified > this way.
Docs mention it though in very vague way. Here http://www.sqlite.org/lang_aggfunc.html in the second paragraph: "In any aggregate function that takes a single argument, that argument can be preceded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will return the number of distinct values of column X instead of the total number of non-null values in column X." :-) Pavel On Sat, Oct 17, 2009 at 11:09 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Pavel Ivanov wrote: >>> select * from foo f1 where >>> (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and >>> f2.b in (...) )) = >>> length_of_b_list >>> and b in (...); >> >> Shouldn't this be simplified like this? >> >> select * from foo f1 where >> (select count(distinct b) from foo f2 where f1.a = f2.a and f2.b in (...) ) = >> length_of_b_list >> and b in (...); > > I wasn't sure SQLite supported count(distinct). The docs don't seem to > mention it. But yes, apparently it does and the statement can be simplified > this way. > > Igor Tandetnik > > _______________________________________________ > 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
