Thanks Clemens. I notice however that nobody has attempted to respond to my original questions. I'm mostly interested in why the documentation states that DISTINCT can only be used with aggregate functions taking a single argument, which in fact is only one, namely GROUP_CONCAT. For a person like me with no knowledge of the internals of SQLite it's somewhat puzzling that GROUP_CONCAT(DISTINCT ABC) is valid but GROUP_CONCAT(DISCRETE ABC,' ') is not. The internal logic must/should IMHO be identical in both cases. It doesn't make sense to me. Anyone?
Staffan On Sun, Jan 11, 2015 at 2:22 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
