On 2018/10/12 7:38 AM, John Found wrote:
The following code does not work, but gives an idea what I want to do:
create table t (a, b);
select
group_concat(b) as list
from t
group by a
having ?1 in (list);
i.e. how to select only the groups that contain
some value in the set of values in a column not
specified in group by clause.
My favourite choice is always to try and state the query as close to
what you need it to do.
In English:
Give me all a's where there is a b value equal to ?1 among it's members
, and then group those lines together (by a value) and concatenate the b
values in the output.
(I hope I understood)
Said the same in SQL:
SELECT a, group_concat(b)
FROM t AS t1
WHERE EXISTS(SELECT 1 FROM t AS t2 WHERE t2.a=t1.a AND t2.b = ?1)
GROUP BY a
Now if you were trying to gain absolute fastest speed, the best would be
to first see which a's has a b value of ?1, then simply join to that
list. Two ways of doing that:
Subquery:
SELECT a, group_concat(b)
FROM (SELECT DISTINCT a FROM t WHERE b=?1) AS X
JOIN t ON t.a = X.a
GROUP BY a
CTE:
WITH X(a) AS (SELECT DISTINCT a FROM t WHERE b=?1)
SELECT a, group_concat(b)
FROM X
JOIN t ON t.a = X.a
GROUP BY a
-- Another option... --
SELECT a, group_concat(b)
FROM t
WHERE t.a IN (SELECT DISTINCT a FROM t WHERE b=?1)
GROUP BY a
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
