mherger wrote:
> As you can see, in the query grouped by namesort "\xC3\x85" would be its
> own item, thus creating its own letter in the index bar. Whereas the
> real query would put it with all the As in the list. I'm no SQLite
> expert, thus might be missing the obvious. But I'd say we're applying
> the same collation there.
Being no SQLite expert, too, I tried several different statements and
found that GROUP BY actually uses a COLLATE sequence.
I downloaded SQLite Expert Pro that provides built-in ICU support
(switched on in the options).
And I used this data in the contributors table:
Code:
--------------------
id namesort
20 ADELE
21 ALANIS MORISSETTE
22 Å NIKKI YANOFSKY
23 A FINE FRENZY
24 XYZ
--------------------
First, I loaded the collation like this: SELECT
icu_load_collation('de_DE', 'de_DE')
Then I executed this:
Code:
--------------------
SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id)
FROM contributors
GROUP BY SUBSTR(contributors.namesort,1,1) COLLATE de_DE
--------------------
which returned this:
Code:
--------------------
A 3
Å 1
X 1
--------------------
When I use GROUP BY without COLLATE I get this:
Code:
--------------------
A 3
X 1
Å 1
--------------------
So the collation sequence is in fact used by the GROUP BY. The ICU
ordering for a given locale can be checked here:
http://demo.icu-project.org/icu-bin/locexp.
Things change when the ORDER BY comes into play:
Code:
--------------------
SELECT SUBSTR(contributors.namesort,1,1), count(distinct contributors.id)
FROM contributors
GROUP BY SUBSTR(contributors.namesort,1,1) COLLATE de_DE
ORDER BY contributors.namesort COLLATE de_DE
--------------------
delivers this:
Code:
--------------------
Å 1
A 3
X 1
--------------------
Now the characters are in the wrong order: A should be before Å, I'd say
(the ORDER BY without COLLATE results in the Å after the X). Specifying
COLLATE in the GROUP BY clause seems to have no effect when the ORDER BY
is following.
I don't have enough SQL knowledge to find an explanation or solution...
------------------------------------------------------------------------
reinholdk's Profile: http://forums.slimdevices.com/member.php?userid=36070
View this thread: http://forums.slimdevices.com/showthread.php?t=101264
_______________________________________________
beta mailing list
beta@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/beta
