Horton

Thread

  1. order by accents?

    Patrick Coulombe <pcoulombe@mediacces.com> — 2000-07-07T16:53:14Z

    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

  2. Re: order by accents?

    ERIC Lawson - x52010 <eric@bioeng.washington.edu> — 2000-07-07T17:19:10Z

    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
>

  3. Re: order by accents?

    ERIC Lawson - x52010 <eric@bioeng.washington.edu> — 2000-07-10T16:41:16Z

    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
>

  4. alias@server9.hypermart.net — 2000-07-10T16:42:51Z

    <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&nbsp; again and use the form available from the "List"
pages.

<P>Ron, Phyllis, and Brian
</BODY>
</HTML>

  5. Re: order by accents?

    Manuel Sugawara <masm@fciencias.unam.mx> — 2000-07-10T19:29:03Z

    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.