Postgres Pro
Downloads
Home   >   mailing lists

Thread: data order problem

data order problem

From
"jack"
Date:
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

Re: data order problem

From
"Josh Berkus"
Date:
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

Re: data order problem

From
Tom Lane
Date:
"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

Re: data order problem

From
"jack"
Date:
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
>

Re: data order problem

From
"Josh Berkus"
Date:
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

Re: data order problem

From
Stephan Szabo
Date:
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.

MAX(column1),MAX(column2),...

From
Bjarte Aagnes
Date:
Hi,

How do I list all column names and maximum values for each column in a table.
I can list the column names, but can't see how to get the maximum of each
column without typing max(name) for every column.

Regards
Bjarte Aagnes

Re: MAX(column1),MAX(column2),...

From
"Josh Berkus"
Date:
Bjarte,

> How do I list all column names and maximum values for each column in
> a table.
> I can list the column names, but can't see how to get the maximum of
> each
> column without typing max(name) for every column.

That is exactly what you have to do.   I do not understand the problem.If you are looking for some way to save yourself
typingthe column
 
names, it doesn't exist.

-Josh Berkus

Re: MAX(column1),MAX(column2),...

From
"Travis Hoyt"
Date:
You could write a plpgsql function to select all the column names from the
one of the system tables and do max($myvar) for each of the column names
returned.

select attname from pg_stats where tablename = 'yourtablename';

Will get you the list of columns.  Just write a loop the loop through each
one and do max().

Travis

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Monday, April 15, 2002 3:34 PM
To: Bjarte Aagnes; pgsql-sql@postgresql.org
Subject: Re: [SQL] MAX(column1),MAX(column2),...


Bjarte,

> How do I list all column names and maximum values for each column in
> a table.
> I can list the column names, but can't see how to get the maximum of
> each
> column without typing max(name) for every column.

That is exactly what you have to do.   I do not understand the problem.If you are looking for some way to save yourself
typingthe column
 
names, it doesn't exist.

-Josh Berkus

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org