Ohh! Look at this... I think query #4 does not handle the accents
correctly, and it should behave almost like #5 does, except for the record
with p = 'R' which should be at the top (if they were #4's results).
#5 is only shown as a sample. #5 works correctly.
I'm using PGSQL 7.3.2 under Redhat Linux 8.0. The database was initialized
with --lc-collate=es_MX.
1. This is the table:
testdb=# \d t
Table "public.t"
Column | Type | Modifiers
--------+-----------------------+-----------
p | character varying(20) |
m | character varying(20) |
2. This is the data:
testdb=# SELECT * FROM t;
p | m
----+------
ra | ab
re | cc
ri | cd
ré | gg
ré | zz
RE | t3
R | tÉ3
RÉ | t3
RÉ | t4
RÍ | am4
Rí | am 5
(11 rows)
3. I think the following is correct:
testdb=# SELECT * FROM t ORDER BY p;
p | m
----+------
R | tÉ3
ra | ab
re | cc
RE | t3
ré | gg
ré | zz
RÉ | t3
RÉ | t4
ri | cd
Rí | am 5
RÍ | am4
(11 rows)
4. The following is NOT correct. Take a look at #5 for the almost-correct
behavior.
testdb=# SELECT * FROM t ORDER BY p, m;
p | m
----+------
R | tÉ3
ra | ab
re | cc
RE | t3
ré | gg
ré | zz
RÉ | t3
RÉ | t4
ri | cd
Rí | am 5
RÍ | am4
(11 rows)
5. The following shows the result somewhat like they should be. Actually,
the "R" alone should be at the beginning (not in this query, but if these
were the results of the previous one. This one is working ok.)
testdb=# SELECT * FROM t ORDER BY p || m;
p | m
----+------
ra | ab
re | cc
ré | gg
RE | t3
RÉ | t3
RÉ | t4
ré | zz
RÍ | am4
Rí | am 5
ri | cd
R | tÉ3
(11 rows)
--
Octavio Alvarez Piza.
E-mail: alvarezp@octavio.ods.org