Staffan Tylen wrote:
> Well, the SELECT is actually over 400 lines long [...] I
> can't use SELECT DISTINCT X as that wouldn't give the result I want, and I
> can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment
> doesn't actually hold. I see no other way than to use DISTINCT with the
> GROUP_CONCAT function, which in this case is invalid.
You could move the aggregate function into a correlated subquery:
SELECT x,
GROUP_CONCAT(y)
FROM T1
GROUP BY x
=>
SELECT x,
(SELECT GROUP_CONCAT(y)
FROM T1 as T1_inner
WHERE T1_inner.x = T1.x)
FROM T1
GROUP BY x
This requires duplicating all the joins and WHEREs from the outer query,
but allows you to handle the inner query differently:
SELECT x,
(SELECT GROUP_CONCAT(y, ' ')
FROM (SELECT DISTINCT y
FROM T1 as T1_inner
WHERE T1_inner.x = T1.x))
FROM T1
GROUP BY x
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
