Обсуждение: order by accents?
hi, if I do a query like this one : SELECT name from medias ORDER BY name name ---- AAAA CCCC EEEE VVVV ZZZZ ÉCCC ---- 6 rows Why the record : ÉCCC is at the end? HOW can I fix this? Thank you Patrick
Similarly, if I order by last name, last names consisting of two or more words (e.g., "van Agoden") fall at the end of the list, after last names beginning with zed. I would expect Val, JB Van, ER Vanel, SV Van Agoden, BT or some similar ordering, but not Val, JB Van, ER Vanel, SV ... Zag, SH Zuli, TP Van Agoden, BT etc. I'm using version 6.5.3. James Eric Lawson Research Publications Editor III National Simulation Resource eric@bioeng.washington.edu On Fri, 7 Jul 2000, Patrick Coulombe wrote: > hi, > if I do a query like this one : > > SELECT name from medias ORDER BY name > > name > ---- > AAAA > CCCC > EEEE > VVVV > ZZZZ > ÉCCC > ---- > 6 rows > > > Why the record : ÉCCC is at the end? > HOW can I fix this? > > Thank you > Patrick >
Hi,
I found a work-around for my problem (controlling the results of an "order
by") after observing that the sort-order was ascii-based. By using the
function lower(fieldname) or upper(fieldname), I get something
approximating what I would expect of an ordered last name list.
I suggest, non-authoritatively, that your problem with the accented E
character has a similar work-around by way of the translate function,
i.e., for the one character, something like
order by translate('fieldname','[accented E character]','E')
For more than one character, (e.g., accented E and accented U), it would
probably be best to write a function that translates all the characters
into characters that will fall in the correct spot in your list order.
Please note that I've very little idea what I'm talking about. For
example, I assume the sort order is locale-based, and will thus be
different for locales where the collating sequence is other than ASCII;
further, I assume the sort order can be tweaked via mechanisms with which
I'm totally unfamiliar.
Eric
James Eric Lawson
Research Publications Editor III
National Simulation Resource
eric@bioeng.washington.edu
On Fri, 7 Jul 2000, Patrick Coulombe wrote:
> hi,
> if I do a query like this one :
>
> SELECT name from medias ORDER BY name
>
> name
> ----
> AAAA
> CCCC
> EEEE
> VVVV
> ZZZZ
> ÉCCC
> ----
> 6 rows
>
>
> Why the record : ÉCCC is at the end?
> HOW can I fix this?
>
> Thank you
> Patrick
>
ERIC Lawson - x52010 <eric@bioeng.washington.edu> writes: > > > hi, > > if I do a query like this one : > > > > SELECT name from medias ORDER BY name > > > > name > > ---- > > AAAA > > CCCC > > EEEE > > VVVV > > ZZZZ > > �CCC > > ---- > > 6 rows > > > > > > Why the record : �CCC is at the end? > > HOW can I fix this? By default postgres uses the standard "C" locale to sort it's results. Is your postgres installation compiled with locale support?, If it is, you can use environment variables such as LC_CTYPE to tell postgres how to sort characters. For example with LC_CTYPE=es (Spanish) your select will return: name ---- AAAA CCCC �CCC EEEE VVVV ZZZZ ---- 6 rows Which may be what you was expecting. HTH. Regards, Manuel.
<HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Author" CONTENT="Ronald Barnes"> <META NAME="GENERATOR" CONTENT="Mozilla/4.04 [en]C-NECCK (Win16; I) [Netscape]"> <TITLE>Kwench Autoresponse</TITLE> </HEAD> <BODY TEXT="#3333FF" BGCOLOR="#66FFFF" LINK="#006600" VLINK="#3333FF" ALINK="#33FF33" BACKGROUND="kwenchfd.gif"> <!--#echo banner=""--> Return-Path: Nightowl@rgbarnes.hypermart.net <BR>From: Nightowl@rgbarnes.hypermart.net <BR>Subject: Thank you for contacting Kwench Refreshments <P>We hope you enjoyed your visit to our web site. If you are in the business and would like more information about Kwench please contact us at the email address above. If you would like to list your web site or leave a mailing address or phone number please send that information to us via email or visit kwench.com again and use the form available from the "List" pages. <P>Ron, Phyllis, and Brian </BODY> </HTML>