Re: [sqlite] GROUP_CONCAT separator and DISTINCT

[Don V Nielsen]

I think David Blake is suggesting that GROUP_CONCAT intuitively suggests it
should take the separator argument, regardless of DISTINCT being present.
It is logical that it should, given GROUP_CONCAT takes two arguments, not
one. The second argument defaults to a comma when omitted. The presence of
DISTINCT is really a lexical issue; an attribute of the first argument and
not an argument itself.

Using replace() or a subquery are workarounds, which is how they look and
feel. Using replace() is the most logical workaround...unless...the column
already contains a comma. In that case, then an awkward subquery is
required. This eventually leads one to think "what is this guy trying to
accomplish" when one reads it.

Just my two cents

On Fri, Aug 25, 2017 at 3:01 AM, Clemens Ladisch  wrote:

> Dave Blake wrote:
> > It seems that it is not possible to specify the concatenation separator
> > when using GROUP_CONCAT with DISTINCT.
>
> The documentation  says:
> | In any aggregate function that takes a single argument, that argument
> | can be preceded by the keyword DISTINCT.
>
> > Is there another way I can specify the separator when using DISTINCT?
>
> If your values do not contain commas, you can use replace() afterwards.
>
> Otherwise, use a subquery with DISTINCT first, and then run the
> group_concat() over that.
>
>
> Regars,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT separator and DISTINCT

[Clemens Ladisch]

Dave Blake wrote:
> It seems that it is not possible to specify the concatenation separator
> when using GROUP_CONCAT with DISTINCT.

The documentation  says:
| In any aggregate function that takes a single argument, that argument
| can be preceded by the keyword DISTINCT.

> Is there another way I can specify the separator when using DISTINCT?

If your values do not contain commas, you can use replace() afterwards.

Otherwise, use a subquery with DISTINCT first, and then run the
group_concat() over that.


Regars,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GROUP_CONCAT separator and DISTINCT

[Dave Blake]

It seems that it is not possible to specify the concatenation separator
when using GROUP_CONCAT with DISTINCT.

For example while this works

SELECT pub_id, GROUP_CONCAT(cate_id, " - ")
FROM book_mast
GROUP BY pub_id;

and this works

SELECT pub_id, GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id;

this does not

SELECT pub_id, GROUP_CONCAT(DISTINCT cate_id, " - ")
FROM book_mast
GROUP BY pub_id;

Is that an error, or by design?
Is there another way I can specify the separator when using DISTINCT?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users