Thread: Problem with ORDER BY

Problem with ORDER BY

From
lsunley@mb.sympatico.ca
Date:
The previous one of these seems to being blocked because of the big
attachments.

Short stuff is embedded in this e-mail.


Hi all,

I have a problem in my os/2 port of postgreSQL v8.0 and I am hoping
someone can help me with it.

When I create a database and use the commands shown below the  select ...
order by works for the pg_tables view column "tablename" but  fails for
the addresslist column "city". The output from my test database is in
out.log

I ran a complete createdb processed the addresslist.sql with -d3 output
and the results are in debug.log and psql output in out2.log. There was a
failure when I typoed the createdb statement.

select ... order by will work for the columns defined as integer but not
for character

Where is the best place to start looking???

Does this work properly on other platforms (I hope so).

Thanks for any help.

Lorne Sunley

------------ SQL to execute --------------
create table addresslist (name char(40), address char(40), city char(40),
telephone char(20), postalcode char(10), numfield integer); insert into
addresslist values ('Aardvark', '101 Street', 'Sometown', '999-000', 'H0H
0H0', 1); insert into addresslist values ('Beebop', '901 Street',
'Sometown', '888-000', 'T0T 0H0', 2); insert into addresslist values
('Xerox', '801 Street', 'Sometown', '777-000', 'K0H 0H0', 3); insert into
addresslist values ('Middling', '1101 Street', 'Sometown', '333-000', 'R0R
0H0', 4); insert into addresslist values ('Lost', '1 Avenue', 'Sometown',
'456-000', 'X0X 0H0', 5); insert into addresslist values ('Timeout', '202
Street', 'Sometown', '789-000', 'T0H 0H0' ,5); insert into addresslist
values ('What Where Who', '555 Street', 'Where', '911-000', 'T0H 0H0', 6);
insert into addresslist values ('Catalog', '989 Street', 'LittleBurg',
'911-001', 'T0H 0H0', 7); insert into addresslist values ('Premium', '21
Street', 'Bigcity', '222-4444', 'W0W 0H0', 8); insert into addresslist
values ('ToastMaster', '13 Street', 'Waytown', '444-000', 'W0R 0H0', 9);
insert into addresslist values ('Dumbthing', '888 Street', 'Thistown',
'555-000', 'W0T 0H0', 9); select city from addresslist order by city desc;
select tablename from pg_tables order by tablename desc;
select city from addresslist order by city asc;
select tablename from pg_tables order by tablename asc;
select city, numfield from addresslist order by numfield asc; select city,
numfield from addresslist order by numfield desc;

-----------  end of SQL -------------------------


--------- results -------------------------------

CREATE TABLE
INSERT 17325 1
INSERT 17326 1
INSERT 17327 1
INSERT 17328 1
INSERT 17329 1
INSERT 17330 1
INSERT 17331 1
INSERT 17332 1
INSERT 17333 1
INSERT 17334 1
INSERT 17335 1                  city                   
------------------------------------------Thistown                                Sometown
 Sometown                                Sometown                                Sometown
Sometown                                Where                                   LittleBurg
Bigcity                                Waytown                                 Sometown

(11 rows)
       tablename        

-------------------------sql_sizing_profilessql_sizingsql_packagessql_languagessql_implementation_infosql_featurespg_typepg_triggerpg_tablespacepg_statisticpg_shadowpg_rewritepg_procpg_operatorpg_opclasspg_namespacepg_listenerpg_largeobjectpg_languagepg_inheritspg_indexpg_grouppg_descriptionpg_dependpg_databasepg_conversionpg_constraintpg_classpg_castpg_attributepg_attrdefpg_amprocpg_amoppg_ampg_aggregateaddresslist
(36 rows)
                  city                   
------------------------------------------Thistown                                Sometown
 Sometown                                Sometown                                Sometown
Sometown                                Where                                   LittleBurg
Bigcity                                Waytown                                 Sometown

(11 rows)
       tablename        

-------------------------addresslistpg_aggregatepg_ampg_amoppg_amprocpg_attrdefpg_attributepg_castpg_classpg_constraintpg_conversionpg_databasepg_dependpg_descriptionpg_grouppg_indexpg_inheritspg_languagepg_largeobjectpg_listenerpg_namespacepg_opclasspg_operatorpg_procpg_rewritepg_shadowpg_statisticpg_tablespacepg_triggerpg_typesql_featuressql_implementation_infosql_languagessql_packagessql_sizingsql_sizing_profiles
(36 rows)
                  city                   | numfield 
------------------------------------------+----------Sometown                                 |        1Sometown
                        |        2Sometown                                 |        3Sometown
     |        4Sometown                                 |        5Sometown                                 |
5Where                                   |        6LittleBurg                               |        7Bigcity
                      |        8Waytown                                  |        9Thistown
   |        9
 
(11 rows)
                  city                   | numfield 
------------------------------------------+----------Waytown                                  |        9Thistown
                        |        9Bigcity                                  |        8LittleBurg
     |        7Where                                    |        6Sometown                                 |
5Sometown                                |        5Sometown                                 |        4Sometown
                      |        3Sometown                                 |        2Sometown
   |        1
 
(11 rows)


--------- end of results -------------------------



-- 
-----------------------------------------------------------
lsunley@mb.sympatico.ca
-----------------------------------------------------------