Just to add: my questions were - why this limitation with DISTINCT, and can
it be changed at least for GROUP_CONCAT?
On Sun, Jan 11, 2015 at 1:23 AM, Staffan Tylen <staffan.ty...@gmail.com>
wrote:
> Well, the SELECT is actually over 400 lines long so 'visualizing' it
> wouldn't be very easy :) But it's along these lines:
>
> SELECT X FROM
> (SELECT 'ABC'||
> IFNULL(' PARM('||GROUP_CONCAT(COL1,' ')||')'),' ')||
> etc
> FROM T1
> LEFT JOIN T2
> LEFT JOIN T3
> etc etc (lots of joins)
> UNION
> SELECT 'DEF'||
> etc
> UNION
> etc
> )
>
> So in this case COL1 might contain duplicates that need to be filtered. 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.
>
> Staffan
>
>
>
>
>
> On Sun, Jan 11, 2015 at 1:00 AM, John McKown <john.archie.mck...@gmail.com
> > wrote:
>
>> On Sat, Jan 10, 2015 at 5:56 PM, Staffan Tylen <staffan.ty...@gmail.com>
>> wrote:
>>
>> > Thanks Richard but unfortunately I cannot use DISTINCT in that way,
>> since
>> > it affects all the selected columns and they are MANY.
>> >
>> > Staffan
>> >
>>
>> I am having trouble visualizing what your actual SELECT is. Would you
>> mind
>> posting it?
>>
>>
>>
>> >
>> >
>> > On Sun, Jan 11, 2015 at 12:43 AM, Richard Hipp <d...@sqlite.org> wrote:
>> >
>> > > On 1/10/15, Staffan Tylen <staffan.ty...@gmail.com> wrote:
>> > > > I'm in the situation where I need to use GROUP_CONCAT and filter out
>> > > > duplicates at the same time. And the default comma separator in
>> > > > GROUP_CONCAT needs to be replaced by a space. I've tried to use
>> > function
>> > > > REPLACE to get rid of the comma but only to realise that the data
>> being
>> > > > concatenated also might contain one or more commas.
>> > > >
>> > >
>> > > SELECT group_concat(x,'+') FROM (SELECT DISTINCT a+b AS x FROM tab
>> ORDER
>> > > BY 1);
>> > >
>> > >
>> > > --
>> > > D. Richard Hipp
>> > > d...@sqlite.org
>> > > _______________________________________________
>> > > 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
>> >
>>
>>
>>
>> --
>>
>> While a transcendent vocabulary is laudable, one must be eternally careful
>> so that the calculated objective of communication does not become
>> ensconced
>> in obscurity. In other words, eschew obfuscation.
>>
>> 111,111,111 x 111,111,111 = 12,345,678,987,654,321
>>
>> Maranatha! <><
>> John McKown
>> _______________________________________________
>> 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
