pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
data order problem
6+ messages / 4 participants
[nested] [flat]

* data order problem
@ 2002-04-15 03:19 jack <datactrl@tpg.com.au>
  2002-04-15 04:53 ` Re: data order problem Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: jack @ 2002-04-15 03:19 UTC (permalink / raw)
  To: pgsql-sql

Hi, all

I use psql v7.2. I have a table which has one column called suburb( and
primary key as well) and data is such as..

THE AAA
THE BBB
THE CDD
THECCC

After I do
select * from xxx order by suburb;
It shows as..

THE AAA
THE BBB
THECCC
THE CDD

why is it not ordered by ASCII sequence. And I found if I put in a data such
as '<ABC>', it goes to last one when you do the same query.

Thank you!

JACK







^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: data order problem
  2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
@ 2002-04-15 04:53 ` Josh Berkus <josh@agliodbs.com>
  2002-04-15 04:55   ` Re: data order problem Tom Lane <tgl@sss.pgh.pa.us>
  2002-04-15 06:40   ` Re: data order problem jack <datactrl@tpg.com.au>
  0 siblings, 2 replies; 6+ messages in thread

From: Josh Berkus @ 2002-04-15 04:53 UTC (permalink / raw)
  To: jack <datactrl@tpg.com.au>; pgsql-sql

Jack,

> why is it not ordered by ASCII sequence. And I found if I put in a
> data such
> as '<ABC>', it goes to last one when you do the same query.

I'm sorry, you're going to have to be more explicit.  I don't
understand what problem you are having with the order given.

-Josh Berkus



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: data order problem
  2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
  2002-04-15 04:53 ` Re: data order problem Josh Berkus <josh@agliodbs.com>
@ 2002-04-15 04:55   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-04-15 04:55 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: jack <datactrl@tpg.com.au>; pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
>> why is it not ordered by ASCII sequence. And I found if I put in a
>> data such
>> as '<ABC>', it goes to last one when you do the same query.

> I'm sorry, you're going to have to be more explicit.  I don't
> understand what problem you are having with the order given.

I think he's using a non-C locale and expecting C-locale sort order.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: data order problem
  2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
  2002-04-15 04:53 ` Re: data order problem Josh Berkus <josh@agliodbs.com>
@ 2002-04-15 06:40   ` jack <datactrl@tpg.com.au>
  2002-04-15 15:46     ` Re: data order problem Josh Berkus <josh@agliodbs.com>
  2002-04-15 17:45     ` Re: data order problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 2 replies; 6+ messages in thread

From: jack @ 2002-04-15 06:40 UTC (permalink / raw)
  To: pgsql-sql; Josh Berkus <josh@agliodbs.com>

Hi, Jose

Suppose '_' stands for a space character. The one column table,xxx, contains
following data.
column name is suburb and is set as primary key.

'THE_AAA'
'THE_BBB'
'THE_CDD'
'THECCC'

After I do
"SELECT * FROM xxx ORDER BY suburb;"
I've got this.

'THE_AAA'
'THE_BBB'
'THECCC'
'THE_CDD'

But I expect it should be like ...
'THE_AAA'
'THE_BBB'
'THE_CDD'
'THECCC'

Because, according to ascii code, space character should be smaller than 'C'


----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "jack" <datactrl@tpg.com.au>; <pgsql-sql@postgresql.org>
Sent: Monday, April 15, 2002 2:53 PM
Subject: Re: [SQL] data order problem


> Jack,
>
> > why is it not ordered by ASCII sequence. And I found if I put in a
> > data such
> > as '<ABC>', it goes to last one when you do the same query.
>
> I'm sorry, you're going to have to be more explicit.  I don't
> understand what problem you are having with the order given.
>
> -Josh Berkus
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: data order problem
  2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
  2002-04-15 04:53 ` Re: data order problem Josh Berkus <josh@agliodbs.com>
  2002-04-15 06:40   ` Re: data order problem jack <datactrl@tpg.com.au>
@ 2002-04-15 15:46     ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2002-04-15 15:46 UTC (permalink / raw)
  To: jack <datactrl@tpg.com.au>; pgsql-sql; Josh Berkus <josh@agliodbs.com>

Jack,

> Suppose '_' stands for a space character. The one column table,xxx,
> contains
> following data.
> column name is suburb and is set as primary key.
> 
> 'THE_AAA'
> 'THE_BBB'
> 'THE_CDD'
> 'THECCC'
> 
> After I do
> "SELECT * FROM xxx ORDER BY suburb;"
> I've got this.
> 
> 'THE_AAA'
> 'THE_BBB'
> 'THECCC'
> 'THE_CDD'
> 
> But I expect it should be like ...
> 'THE_AAA'
> 'THE_BBB'
> 'THE_CDD'
> 'THECCC'
> 
> Because, according to ascii code, space character should be smaller
> than 'C'

It is.  I'm afraid that you compiled your database with some kind of
non-standard sort order, possibly due to the OS you are using.  I can't
help you with this; I'm just a SQL junkie.  Good luck!

-Josh



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: data order problem
  2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
  2002-04-15 04:53 ` Re: data order problem Josh Berkus <josh@agliodbs.com>
  2002-04-15 06:40   ` Re: data order problem jack <datactrl@tpg.com.au>
@ 2002-04-15 17:45     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Stephan Szabo @ 2002-04-15 17:45 UTC (permalink / raw)
  To: jack <datactrl@tpg.com.au>; +Cc: pgsql-sql; Josh Berkus <josh@agliodbs.com>

On Mon, 15 Apr 2002, jack wrote:

> Hi, Jose
>
> Suppose '_' stands for a space character. The one column table,xxx, contains
> following data.
> column name is suburb and is set as primary key.
>
> 'THE_AAA'
> 'THE_BBB'
> 'THE_CDD'
> 'THECCC'
>
> After I do
> "SELECT * FROM xxx ORDER BY suburb;"
> I've got this.
>
> 'THE_AAA'
> 'THE_BBB'
> 'THECCC'
> 'THE_CDD'
>
> But I expect it should be like ...
> 'THE_AAA'
> 'THE_BBB'
> 'THE_CDD'
> 'THECCC'
>
> Because, according to ascii code, space character should be smaller than 'C'

If you put the above data in a text file and use the sort command, I'd
expect that you get the same ordering as postgres because the machine is
probably not set to the 'C' locale, it's probably set to something like
'en_AU' which may have different sort order rules.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-04-15 17:45 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-15 03:19 data order problem jack <datactrl@tpg.com.au>
2002-04-15 04:53 ` Josh Berkus <josh@agliodbs.com>
2002-04-15 04:55   ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-15 06:40   ` jack <datactrl@tpg.com.au>
2002-04-15 15:46     ` Josh Berkus <josh@agliodbs.com>
2002-04-15 17:45     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox