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.
Understood. But wouldn't a GROUP BY sort the data internally in order to
be able to group records? Or would you not at least expect it to follow
the same rules grouping as when sorting? In sorting it seems to consider
Š "the same" as S, but it doesn't in grouping. I'm not too concerned
about the representation.
Michael
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
