Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sorting when "*" is the initial character

Sorting when "*" is the initial character

From
"Berend Tober"
Date:
I encountered what looks like unusually sorting behavior, and I'm wondering if
anyone can tell me if this is supposted to happen (and then if so, why) or if
this is a bug:

CREATE TABLE sample_table
(
  account_id varchar(4),
  account_name varchar(25)
)
WITHOUT OIDS;

INSERT INTO sample_table VALUES ('100', 'First account');
INSERT INTO sample_table VALUES ('110', 'Second account');
INSERT INTO sample_table VALUES ('120', 'Third account');
INSERT INTO sample_table VALUES ('*125', 'Fourth account');
INSERT INTO sample_table VALUES ('*115', 'Fifth account');

SELECT * FROM sample_table ORDER BY 1;

account_id,account_name
100,First account
110,Second account
*115,Fifth account
120,Third account
*125,Fourth account

I would expect to see

account_id,account_name
*115,Fifth account
*125,Fourth account
100,First account
110,Second account
120,Third account

Re: Sorting when "*" is the initial character

From
Bruno Wolff III
Date:
On Mon, Feb 07, 2005 at 16:20:36 -0500,
  Berend Tober <btober@seaworthysys.com> wrote:
>
> SELECT * FROM sample_table ORDER BY 1;
>
> account_id,account_name
> 100,First account
> 110,Second account
> *115,Fifth account
> 120,Third account
> *125,Fourth account
>
> I would expect to see
>
> account_id,account_name
> *115,Fifth account
> *125,Fourth account
> 100,First account
> 110,Second account
> 120,Third account

This depends on your locale.

Re: Sorting when "*" is the initial character

From
Stephan Szabo
Date:
On Mon, 7 Feb 2005, Berend Tober wrote:

> I encountered what looks like unusually sorting behavior, and I'm wondering if
> anyone can tell me if this is supposted to happen (and then if so, why) or if
> this is a bug:

If you ran initdb with a locale such as en_US, a result like what you got
is expected.  AFAIR, the collation rules for the locale are defined to not
use symbols and spaces in the first pass comparison so '110' < '*115' <
'120'.

Re: Sorting when "*" is the initial character

From
CoL
Date:
hi,

Berend Tober wrote, On 2/7/2005 22:20:
> I encountered what looks like unusually sorting behavior, and I'm wondering if
> anyone can tell me if this is supposted to happen (and then if so, why) or if
> this is a bug:
>
> CREATE TABLE sample_table
> (
>   account_id varchar(4),
>   account_name varchar(25)
> )
> WITHOUT OIDS;
>
> INSERT INTO sample_table VALUES ('100', 'First account');
> INSERT INTO sample_table VALUES ('110', 'Second account');
> INSERT INTO sample_table VALUES ('120', 'Third account');
> INSERT INTO sample_table VALUES ('*125', 'Fourth account');
> INSERT INTO sample_table VALUES ('*115', 'Fifth account');
>
> SELECT * FROM sample_table ORDER BY 1;
>
> account_id,account_name
> 100,First account
> 110,Second account
> *115,Fifth account
> 120,Third account
> *125,Fourth account
>
> I would expect to see
>
> account_id,account_name
> *115,Fifth account
> *125,Fourth account
> 100,First account
> 110,Second account
> 120,Third account

order by case when account_id like '*%' then 0 else 1 end

C.

Re: Sorting when "*" is the initial character

From
Russell Smith
Date:
On Tue, 8 Feb 2005 01:10 pm, CoL wrote:
> hi,
>
> Berend Tober wrote, On 2/7/2005 22:20:
> > I encountered what looks like unusually sorting behavior, and I'm wondering if
> > anyone can tell me if this is supposted to happen (and then if so, why) or if
> > this is a bug:
> >
> >
> > SELECT * FROM sample_table ORDER BY 1;
> >
> > account_id,account_name
> > 100,First account
> > 110,Second account
> > *115,Fifth account
> > 120,Third account
> > *125,Fourth account
> >
> > I would expect to see
> >
> > account_id,account_name
> > *115,Fifth account
> > *125,Fourth account
> > 100,First account
> > 110,Second account
> > 120,Third account

With 8.0.0  C local, SQL_ASCII Database, I get the expected output.

Regards

Russell Smith

Re: Sorting when '*' is the initial character - solved

From
"Berend Tober"
Date:
> On Tue, 8 Feb 2005 01:10 pm, CoL wrote:
>> hi,
>>
>> Berend Tober wrote, On 2/7/2005 22:20:
>> > I encountered what looks like unusually sorting behavior, and I'm
>> wondering if
>> > anyone can tell me if this is supposted to happen (and then if so, why) or
>> if
>> > this is a bug:

--------------
> With 8.0.0  C local, SQL_ASCII Database, I get the expected output.
> Russell Smith
--------------
> order by case when account_id like '*%' then 0 else 1 end
> C.

Thanks. It was pointed out to me that this behavior is normal and is dependent
on the locale setting.