X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8DCC39DC81C for ; Fri, 3 Mar 2006 05:50:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09799-07 for ; Fri, 3 Mar 2006 05:50:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.hive.is (scania.ipf.is [85.197.192.147]) by postgresql.org (Postfix) with SMTP id 377149DC84E for ; Fri, 3 Mar 2006 05:50:21 -0400 (AST) Received: (qmail 54247 invoked by uid 1010); 3 Mar 2006 09:50:37 -0000 Received: from 85.197.216.186 by scania.ipf.is (envelope-from , uid 1009) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1283. spamassassin: 3.1.0. perlscan: 1.25-st-qms. Clear:RC:1(85.197.216.186):SA:0(-4.1/4.0):. Processed in 0.621465 secs); 03 Mar 2006 09:50:37 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via scania.ipf.is X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(85.197.216.186):SA:0(-4.1/4.0):. Processed in 0.621465 secs Process 54240) Received: from dsl-216-186.hive.is (HELO ?192.168.1.34?) (85.197.216.186) by mx1.hive.is with SMTP; 3 Mar 2006 09:50:36 -0000 Subject: Re: Why do I get these results????? From: Ragnar To: Joost Kraaijeveld Cc: Pgsql-Sql In-Reply-To: <1141375840.4267.16.camel@localhost> References: <1141375840.4267.16.camel@localhost> Content-Type: text/plain; charset=ISO-8859-1 Date: Fri, 03 Mar 2006 09:51:17 +0000 Message-Id: <1141379477.18656.130.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.151 required=5 tests=[AWL=0.151] X-Spam-Score: 0.151 X-Spam-Level: X-Archive-Number: 200603/65 X-Sequence-Number: 24411 On fös, 2006-03-03 at 09:50 +0100, Joost Kraaijeveld wrote: > Hi, > > Why do I get the following result from the query below? I expected that, > given the fact that there are over 100 "Jansen" (but no "jansen") in > "Nijmegen" the first record would definitively be people living in > "Nijmegen". If I change the order to the order that is commented out, > the query goes OK. > > SELECT > addresses.zipcode, > addresses.city, > addresses.housenumber, > addresses.housenumberdetails, > customers.lastname > FROM prototype.customers JOIN prototype.addresses ON > customers.contactaddress = addresses.objectid > WHERE > TRIM(UPPER(customers.lastname)) >= TRIM(UPPER('Jansen')) > AND > TRIM(UPPER(addresses.city)) >= TRIM(UPPER('NIJMEGEN')) > ORDER BY customers.lastname, addresses.city, addresses.zipcode > --ORDER BY addresses.city, customers.lastname, addresses.zipcode > limit 5 > > Result: > > "3089TN";"ROTTERDAM";"5";"";"jansen" > "5712XG";"SOMEREN";"13";"";"jansen" > "6511PS";"NIJMEGEN";"23";"";"Jansen" > "6523RE";"NIJMEGEN";"13";"";"Jansen" > "6524NP";"NIJMEGEN";"37";"A";"Jansen" What LOCALE are you using? Looks like it either sorts lowercase before uppercase or treats them as equivalent. Why do you not provide us with a simple test case? Why involve a join and irrelevant columns? What does a simple test like this do for you?: test=# create table t (c text, n text); CREATE TABLE test=# insert into t values ('ROTTERDAM','jansen'); INSERT 33566780 1 test=# insert into t values ('SOMEREN','jansen'); INSERT 33566781 1 test=# insert into t values ('NIJMEGEN','Jansen'); INSERT 33566782 1 test=# insert into t values ('NIJMEGEN','Jansen'); INSERT 33566783 1 test=# insert into t values ('NIJMEGEN','Jansen'); INSERT 33566784 1 test=# select * from t ORDER BY n,c; c | n -----------+-------- NIJMEGEN | Jansen NIJMEGEN | Jansen NIJMEGEN | Jansen ROTTERDAM | jansen SOMEREN | jansen (5 rows) (this in in C locale) gnari