Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ordering in SELECT statement

Ordering in SELECT statement

From
"kdealba@uaaan.mx"
Date:

Hello,

I use the following command "SELECT * FROM employees ORDER BY name" (a very simple sql statement) the main thing here is that I get a list ordered without taking into accound the spaces. For example, I get the following listing:
ABAB
AB  CD
ABD  E
AB  EF

and what I need is the following ("old fashion", that is, the "SPACE" is another character whose ASCII value is before any other LATIN letter's!!)
AB  CD
AB  EF
ABAB
ABD  E

Does any one know how to order get an old fashioned SORT??? 

Re: Ordering in SELECT statement

From
Richard Huxton
Date:
kdealba@uaaan.mx wrote:
>
>     Hello,
>
>     I use the following command "SELECT * FROM employees ORDER BY name"
> (a very simple sql statement) the main thing here is that I get a
> list ordered without taking into accound the spaces. For example, I
> get the following listing:
> ABAB
> AB  CD
> ABD  E
> AB  EF
>
>     and what I need is the following ("old fashion", that is, the
> "SPACE" is another character whose ASCII value is before any other
> LATIN letter's!!)

You'll want to read up on locales - you want the "C" locale. I'm afraid
it'll mean dumping your database(s) and running initdb again.

--
   Richard Huxton
   Archonet Ltd

Re: Ordering in SELECT statement

From
Michael Glaesemann
Date:
On Jun 26, 2007, at 14:41 , kdealba@uaaan.mx wrote:
> and what I need is the following ("old fashion", that is, the
> "SPACE" is another character whose ASCII value is before any other
> LATIN letter's!!)
> AB  CD
> AB  EF
> ABAB
> ABD  E
Sorting is defined by the locale settings of your computer. I get the
order you're looking for, and the my computer has LC_COLLATE="C".

$ locale
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL="C"

# select * from sorts order by sort;
sort
-------
AB CD
AB EF
ABAB
ABD E
(4 rows)

I haven't had any experience setting the locale on my computer, but
that might be something you want to look into.

Michael Glaesemann
grzm seespotcode net

Re: Ordering in SELECT statement

From
PFC
Date:
> On Jun 26, 2007, at 14:41 , kdealba@uaaan.mx wrote:
>> and what I need is the following ("old fashion", that is, the "SPACE"
>> is another character whose ASCII value is before any other LATIN
>> letter's!!)
>> AB  CD
>> AB  EF
>> ABAB
>> ABD  E

    What you don't want :

peufeu=> SELECT column1 FROM (VALUES ('ABCD'), ('A BCD'), ('abcd'),
('ABcd'), ('AB'), ('AbC d')) AS foo  ORDER BY column1;
  column1
---------
  AB
  abcd
  AbC d
  ABcd
  ABCD
  A BCD
(6 lignes)

    What you want :

peufeu=> SELECT column1 FROM (VALUES ('ABCD'), ('A BCD'), ('abcd'),
('ABcd'), ('AB'), ('AbC d')) AS foo  ORDER BY string_to_array( column1, '
' );
  column1
---------
  A BCD
  AB
  AbC d
  abcd
  ABcd
  ABCD
(6 lignes)