Обсуждение: Ordering not acting right.

I have a text field which I use to store names ( lastname, firstname )
and suppose we have the following 3 authors in the field:
   ta, a
   ta, z
   tab, a

I would expect them to show up in the order shown above when I select
this field and order it by it, but instead I get:

   ta, a
   tab, a
   ta, z

Some one has suggested that this may have to do with my locale setting.
I'm not an administrator so I'm not sure what how to check for the
locale setting or what to change it to.  Does any one have any
suggestions?

I'm using postgres 8.1.11.

Thank you!
Jose

> I have a text field which I use to store names ( lastname,
> firstname )
> and suppose we have the following 3 authors in the field:
>    ta, a
>    ta, z
>    tab, a
>
> I would expect them to show up in the order shown above
> when I select
> this field and order it by it, but instead I get:
>
>    ta, a
>    tab, a
>    ta, z
>
> Some one has suggested that this may have to do with my
> locale setting.
> I'm not an administrator so I'm not sure what how
> to check for the
> locale setting or what to change it to.  Does any one have
> any
> suggestions?
>
> I'm using postgres 8.1.11.

can you show the query sintax?

Select name from info order by  name;

-----Original Message-----
From: Lennin Caro [mailto:lennin.caro@yahoo.com]
Sent: Wednesday, August 20, 2008 4:26 PM
To: pgsql-admin@postgresql.org; Blanco, Jose
Subject: Re: [ADMIN] Ordering not acting right.

> I have a text field which I use to store names ( lastname, firstname )

> and suppose we have the following 3 authors in the field:
>    ta, a
>    ta, z
>    tab, a
>
> I would expect them to show up in the order shown above when I select
> this field and order it by it, but instead I get:
>
>    ta, a
>    tab, a
>    ta, z
>
> Some one has suggested that this may have to do with my locale
> setting.
> I'm not an administrator so I'm not sure what how to check for the
> locale setting or what to change it to.  Does any one have any
> suggestions?
>
> I'm using postgres 8.1.11.

can you show the query sintax?

On Wed, Aug 20, 2008 at 1:48 PM, Blanco, Jose <blancoj@umich.edu> wrote:
> I have a text field which I use to store names ( lastname, firstname )
> and suppose we have the following 3 authors in the field:
>   ta, a
>   ta, z
>   tab, a
>
> I would expect them to show up in the order shown above when I select
> this field and order it by it, but instead I get:
>
>   ta, a
>   tab, a
>   ta, z
>
> Some one has suggested that this may have to do with my locale setting.
> I'm not an administrator so I'm not sure what how to check for the
> locale setting or what to change it to.  Does any one have any
> suggestions?
>
> I'm using postgres 8.1.11.

The best fix if you want ascii ordering is to re-initdb your db with
locale=C and then reload your data.

There's some stuff you can do with varchar_pattern_ops, but I'm not
sure it will work for order by in your situation.

>
> > I have a text field which I use to store names (
> lastname, firstname )
>
> > and suppose we have the following 3 authors in the
> field:
> >    ta, a
> >    ta, z
> >    tab, a
> >
> > I would expect them to show up in the order shown
> above when I select
> > this field and order it by it, but instead I get:
> >
> >    ta, a
> >    tab, a
> >    ta, z
> >
> > Some one has suggested that this may have to do with
> my locale
> > setting.
> > I'm not an administrator so I'm not sure what
> how to check for the
> > locale setting or what to change it to.  Does any one
> have any
> > suggestions?
> >
> > I'm using postgres 8.1.11.
>
> can you show the query sintax?

ok the result of the query is correct the string 'ta' is minor that 'tab'
then 'ta, a' and 'ta, b' is minor to 'tab, a'. If you split the names you can order this

It does not seem correct. If 'ta, a' and 'ta, b' is minor to 'tab, a'.
you should not get that order.

This is my result and what i think is what you expect.

CREATE TABLE sorting (name varchar);

labb=# \d sorting
          Table "public.sorting"
  Column |       Type        | Modifiers
--------+-------------------+-----------
  name   | character varying |


labb=# INSERT INTO sorting VALUES('ta, a');
labb=# INSERT INTO sorting VALUES('ta, z');
labb=# INSERT INTO sorting VALUES('tab, a');


labb=# SELECT name FROM sorting ORDER BY name ASC;
   name
--------
  ta, a
  ta, z
  tab, a
(3 rows)

labb=# SELECT name FROM sorting ORDER BY name DESC;
   name
--------
  tab, a
  ta, z
  ta, a
(3 rows)

What locale do you have on that database?

Best regards,
Mathias Stjernström

http://www.pastbedti.me/




On 20 aug 2008, at 22.36, Lennin Caro wrote:

>>
>>> I have a text field which I use to store names (
>> lastname, firstname )
>>
>>> and suppose we have the following 3 authors in the
>> field:
>>>   ta, a
>>>   ta, z
>>>   tab, a
>>>
>>> I would expect them to show up in the order shown
>> above when I select
>>> this field and order it by it, but instead I get:
>>>
>>>   ta, a
>>>   tab, a
>>>   ta, z
>>>
>>> Some one has suggested that this may have to do with
>> my locale
>>> setting.
>>> I'm not an administrator so I'm not sure what
>> how to check for the
>>> locale setting or what to change it to.  Does any one
>> have any
>>> suggestions?
>>>
>>> I'm using postgres 8.1.11.
>>
>> can you show the query sintax?
>
> ok the result of the query is correct the string 'ta' is minor that
> 'tab'
> then 'ta, a' and 'ta, b' is minor to 'tab, a'. If you split the
> names you can order this
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin