Received: from u1.abs.net (root@u1.abs.net [207.114.0.131]) by hub.org (8.8.8/8.8.8) with ESMTP id OAA08666 for ; Wed, 23 Sep 1998 14:52:26 -0400 (EDT) (envelope-from daveh@insightdist.com) Received: from insightdist.com (root@localhost) by u1.abs.net (8.9.0/8.9.0) with UUCP id OAA06634 for pgsql-sql@postgreSQL.org; Wed, 23 Sep 1998 14:34:28 -0400 (EDT) Received: from ceodev by insightdist.com (AIX 3.2/UCB 5.64/4.03) id AA52340; Wed, 23 Sep 1998 14:27:53 -0400 Received: from daveh by ceodev (AIX 4.1/UCB 5.64/4.03) id AA37710; Wed, 23 Sep 1998 14:27:52 -0400 Message-Id: <36093E8F.3535253E@insightdist.com> Date: Wed, 23 Sep 1998 14:31:43 -0400 From: David Hartwig Organization: Insight Distribution Systems X-Mailer: Mozilla 4.05 [en] (Win95; I) Mime-Version: 1.0 To: Chris Johnson Cc: Richard Lynch , Patrick Giagnocavo , pgsql-sql@postgreSQL.org Subject: Re: [SQL] case-insensitive SORT BY? References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Here is a 6.4 tidbit. select name where name like '%clinton%' order by upper(name), name; is allowed. (i.e. functions are allowed in the ORDER clause) Like you said, the the second attribute in the ORDER clause will be the tie breaker. Chris Johnson wrote: > Perhaps this should go in the FAQ? I asked it myself several months ago. > > Also note that you probably want to sort by key followed by name ( ie ... > sort by key, name) so that you don't run the risk of getting something > like: > > a > A > B > b > > Chris > -- > I am at one with my duality. > > On Tue, 22 Sep 1998, Richard Lynch wrote: > > > Cheat. Get the name back, *AND* a second "field" with the name in upper > > case, and then sort by that upper-cased name. EG: > > > > "select name, upper(name) as key where name like '%clinton%' sort by key" > > > > At 3:53 PM 9/22/98, Patrick Giagnocavo wrote: > > >I am trying to ensure that when I do a SORT BY I get back the results in > > >normal sorted order instead of case-sensitive order. The WHERE clause > > >contains a LIKE '%..%' so I cannot use UPPER here in a way that does > > >what I want. > > > > > >e.g. , given > > > > > >A, B, b, a > > > > > >as data, the normal SORT BY behavior returns > > > > > >a > > >b > > >A > > >B > > > > > >How do I make it return > > > > > >a > > >A > > >b > > >B > > > > > >instead? > > > > -- > > -- > > -- "TANSTAAFL" Rich lynch@lscorp.com > > > > > >