COLLATE affects SORTING, it does not transmorgify the "value" of the thing to which it is applied. That is, name COLLATE xxxx means that the item name is SORTED using the collating sequence xxxx, not that the result of "name COLLATE xxxx" is transmorgified into tha value that is used for sorting.
That is select name collate nocase, count(distinct id) from x group by name collate nocase order by name collate nocase whill produce cased output not the value that was used for the sorting. select lower(name collate nocase), count(distinct id) from x group by name collate nocase order by name collate nocase; to transmorgificate name into a "caseless" representation. So you would need to do something like this: select de_DE(substr(name collate de_DE,1,1)), count(distinct id) from artists group by substr(name collate de_DE,1,1) order by by substr(name collate de_DE,1,1) and the function de_DE would have to transmorgificate its value to the result you want to see. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of li...@herger.net >Sent: Thursday, 7 February, 2019 05:12 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] GROUP BY and ICU collation > >>> 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 ... > >TBH: I didn't even know about this. I thought the COLLATE at the end >of >the statement would do it for all. > >Alas, tried again to no avail. No matter whether I add it after the >GROUP BY or not, the result is the same. > >I should probably have added some version information: I'm using the >Perl DBD::SQLite 1.58 (sqlite 3.22.0). I didn't see any mention of >related changes in the changelog for SQLite. What would be the >easiest >(and most reliable) way to try to reproduce this without Perl? Is >there >a HowTo use collations with the CLI sqlite? > >-- > >Michael >_______________________________________________ >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
