GROUP_CONCAT separator and DISTINCT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <http://www.sqlite.org/lang_aggfunc.html> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> Dave Blake wrote:
> > It seems that it is not possible to specify the concatenation separator
> > when using GROUP_CONCAT with DISTINCT.
>
> The documentation <http://www.sqlite.org/lang_aggfunc.html> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users