GROUP BY and ICU collation

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

GROUP BY and ICU collation

Michael Herger
Hi there,

I'm trying to create a list with an index list. Eg. I have artists:

Sting
Šuma Čovjek
Suzanne Vega

That's the sort order I'd get using an ICU collation. "Šuma Čovjek"
would be sorted as "Suma..." as expected.

Now I'd like to create an index bar by providing groups of the first
character:

SELECT SUBSTR(name,1,1), count(distinct id) FROM artists GROUP BY
SUBSTR(name,1,1) ORDER BY name COLLATE de_DE

Now this would create two groups for "S" and "Š", leading to unexpected
behaviour in my index bar. It seems as if only ORDER BY would use the
collation, but not GROUP BY. What am I doing wrong?

Michael
_______________________________________________
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 BY and ICU collation

Richard Hipp-3
On 2/6/19, [hidden email] <[hidden email]> wrote:

> Hi there,
>
> I'm trying to create a list with an index list. Eg. I have artists:
>
> Sting
> Šuma Čovjek
> Suzanne Vega
>
> That's the sort order I'd get using an ICU collation. "Šuma Čovjek"
> would be sorted as "Suma..." as expected.
>
> Now I'd like to create an index bar by providing groups of the first
> character:
>
> SELECT SUBSTR(name,1,1), count(distinct id) FROM artists GROUP BY
> SUBSTR(name,1,1) ORDER BY name COLLATE de_DE


Aren't you missing a COLLATE clause after the GROUP BY term?

    ... GROUP BY SUBSTR(name,1,1) COLLATE de_DE ...

>
> Now this would create two groups for "S" and "Š", leading to unexpected
> behaviour in my index bar. It seems as if only ORDER BY would use the
> collation, but not GROUP BY. What am I doing wrong?
>
> Michael
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users