Jeremy Zeiber <blacke...@gmail.com> wrote: > SELECT headerid, > (SELECT COUNT(data) FROM detail AS outerdetail WHERE > headerid=header.headerid AND data NOT IN (SELECT DISTINCT data FROM > detail WHERE headerid<outerdetail.headerid)) AS newcount > FROM header GROUP BY headerid;
Try this: select headerid, count(*) from (select data, min(headerid) first_occured from detail group by data) d_first join header on (header.headerid = d_first.first_occured) group by headerid; An index on detail(data) or detail(data, headerid) may be beneficial (an index on detail(headerid, data) not so much). Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
