John Found wrote: > Clemens Ladisch <clem...@ladisch.de> wrote: >> select >> group_concat(b) as list >> from t >> where a in (select a >> from t >> where b = ?1) >> group by a; >> >> But you will not be able to avoid the subquery: the filter has to >> include all other rows of the group with matching b, and after the >> group_concat(), the result is no longer in a form useful for lookups. > > Hm, is sounds strange because when HAVING clause is processed, > the aggregate functions should not be processed yet (for a performance > reasons) i.e. the query still has access to all values from the field b > and theoretically should be able to search these values the same way > it searches them on executing min() or max() aggregate functions.
Yes, the HAVING clause can run any aggregate function. But you need to find some aggregate function that can do the filtering. PostgreSQL would have "HAVING array_position(array_agg(b), $1)", but I have not been able to get SQLite's json_group_array() to work with this. And "',' || group_concat(b) || ',' LIKE '%,' || ?1 || ',%'" would be horrible. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
