Thread: 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
From
Dragan Matic
Date:
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
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.
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
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
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
From
Tom Lane
Date:
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