Обсуждение: problem with sorting using 'ORDER BY' when character field is filled with numerical values

Поиск
Список
Период
Сортировка

problem with sorting using 'ORDER BY' when character field is filled with numerical values

От
Dragan Matic
Дата:
If I have a table t with column c which is defined as char(5) and fill
it with following values:

insert into t (c) values ('    1')
insert into t (c) values ('    2')
insert into t (c) values ('    3')
insert into t (c) values ('    4')
insert into t (c) values ('   11')
insert into t (c) values ('   12')
insert into t (c) values ('   14')
insert into t (c) values ('   24')
insert into t (c) values ('   21')
insert into t (c) values ('   31')
insert into t (c) values ('  333')

and then do the following: SELECT C FROM T ORDER BY C
Postgres gives me the following

    1
   11
   12
   14
    2
   21
   24
    3
   31
  333
    4

the same thing done with MS SQL server gives this as a result:

    1
    2
    3
    4
   11
   12
   14
   21
   24
   31
  333

which is the result I find more logical, meaning the user would expect
data sorted this way. Is there some way to make Postgres sort elements
in this way (setting sort order or collation, I suppose)? Tnx in advance

Dragan

Re: problem with sorting using 'ORDER BY' when character

От
Guy Fraser
Дата:
Try to cast into an integer.

SELECT C::int4 FROM T ORDER BY C

Just a guess.

Dragan Matic wrote:

> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values ('    1')
> insert into t (c) values ('    2')
> insert into t (c) values ('    3')
> insert into t (c) values ('    4')
> insert into t (c) values ('   11')
> insert into t (c) values ('   12')
> insert into t (c) values ('   14')
> insert into t (c) values ('   24')
> insert into t (c) values ('   21')
> insert into t (c) values ('   31')
> insert into t (c) values ('  333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>    1                11                12
> 14                 2                21                24
>    3                31               333                 4
> the same thing done with MS SQL server gives this as a result:
>
>    1                 2                 3
> 4                11                12                14
> 21                24                31               333
> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)? Tnx in advance
>
> Dragan
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> .
>

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.

Re: problem with sorting using 'ORDER BY' when character

От
Dave Smith
Дата:
Try ..
SELECT C FROM T ORDER BY ltrim(C)

On Thu, 2004-05-20 at 10:49, Dragan Matic wrote:
> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values ('    1')
> insert into t (c) values ('    2')
> insert into t (c) values ('    3')
> insert into t (c) values ('    4')
> insert into t (c) values ('   11')
> insert into t (c) values ('   12')
> insert into t (c) values ('   14')
> insert into t (c) values ('   24')
> insert into t (c) values ('   21')
> insert into t (c) values ('   31')
> insert into t (c) values ('  333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>     1
>    11
>    12
>    14
>     2
>    21
>    24
>     3
>    31
>   333
>     4
>
> the same thing done with MS SQL server gives this as a result:
>
>     1
>     2
>     3
>     4
>    11
>    12
>    14
>    21
>    24
>    31
>   333
>
> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)? Tnx in advance
>
> Dragan
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dave Smith
CANdata Systems Ltd
416-493-9020

Re: problem with sorting using 'ORDER BY' when character

От
Dave Smith
Дата:
Sorry .. I think you mean if the values are not right padded but you want them ordered that way.
Your example works fine here.. unless you have a locale issue ..

try..
SELECT C FROM T ORDER BY lpad(C,5)

On Thu, 2004-05-20 at 10:49, Dragan Matic wrote:
> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values ('    1')
> insert into t (c) values ('    2')
> insert into t (c) values ('    3')
> insert into t (c) values ('    4')
> insert into t (c) values ('   11')
> insert into t (c) values ('   12')
> insert into t (c) values ('   14')
> insert into t (c) values ('   24')
> insert into t (c) values ('   21')
> insert into t (c) values ('   31')
> insert into t (c) values ('  333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>     1
>    11
>    12
>    14
>     2
>    21
>    24
>     3
>    31
>   333
>     4
>
> the same thing done with MS SQL server gives this as a result:
>
>     1
>     2
>     3
>     4
>    11
>    12
>    14
>    21
>    24
>    31
>   333
>
> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)? Tnx in advance
>
> Dragan
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dave Smith
CANdata Systems Ltd
416-493-9020

Re: problem with sorting using 'ORDER BY' when character

От
Stephan Szabo
Дата:
On Thu, 20 May 2004, Dragan Matic wrote:

> If I have a table t with column c which is defined as char(5) and fill
> it with following values:
>
> insert into t (c) values ('    1')
> insert into t (c) values ('    2')
> insert into t (c) values ('    3')
> insert into t (c) values ('    4')
> insert into t (c) values ('   11')
> insert into t (c) values ('   12')
> insert into t (c) values ('   14')
> insert into t (c) values ('   24')
> insert into t (c) values ('   21')
> insert into t (c) values ('   31')
> insert into t (c) values ('  333')
>
> and then do the following: SELECT C FROM T ORDER BY C
> Postgres gives me the following
>
>     1
>    11
>    12
>    14
>     2
>    21
>    24
>     3
>    31
>   333
>     4
>
> the same thing done with MS SQL server gives this as a result:
>
>     1
>     2
>     3
>     4
>    11
>    12
>    14
>    21
>    24
>    31
>   333
>
> which is the result I find more logical, meaning the user would expect
> data sorted this way. Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)? Tnx in advance

You are probably running in a collation that doesn't treat spaces as
particularly significant (for example with a locale of en_US).  If you
want collation by byte order you can use "C" locale (although you need to
re-initdb to change it).

You could also sort it as numbers by converting to a numeric type first
presumably.

Re: problem with sorting using 'ORDER BY' when character field is filled with numerical values

От
Tom Lane
Дата:
Dragan Matic <mlists@panforma.co.yu> writes:
> Is there some way to make Postgres sort elements
> in this way (setting sort order or collation, I suppose)?

C locale would sort that way; you appear to be using some other locale.

I concur with the nearby suggestions that you should consider a more
appropriate datatype, if all your data will be integers.  Operations on
integers will be lots faster than operations on strings.

            regards, tom lane