Обсуждение: data order problem
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
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
"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
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 >
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
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.
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
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
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