Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA83984 for ; Thu, 18 May 2000 18:03:18 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Thu, 18 May 2000 17:03:18 -0500 (CDT) Date: Thu, 18 May 2000 17:03:18 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Library sort order (was Re: SQL command speed) Message-ID: <20000518170318.B2964@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: <3924405E.15014E6B@wsicorp.com> <200005181938.QAA04835@ant.utdt> <20000518153909.B2549@rice.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <20000518153909.B2549@rice.edu>; from reedstrm@ece.rice.edu on Thu, May 18, 2000 at 03:39:09PM -0500 X-Archive-Number: 200005/72 Sorry about the subject: I meant to change it on the last one. As usual, 5 minutes after posting, I came up with a solution, at least for my limited case of ignoring an inital 'The ': SELECT InstName FROM Institutions ORDER BY CASE WHEN strpos(InstName,'The ') = 1 THEN ltrim(InstName,'The ') ELSE InstName END; Note that this requires 7.0, since 6.X won't allow a CASE node in the ORDER BY position. Ross On Thu, May 18, 2000 at 03:39:09PM -0500, Ross J. Reedstrom wrote: > Hey crew: > I've got a relatively simple SQL problem. In a db backed web site > we're building, I'd like to fill a dropdown box with the contents of a > validation table, in this case research institutions. I want to sort them > alphabetically, but using "library rules": i.e. skip inital articles, > since we've a few 'The University of Foo" and "The Johns Hopkins > University", for example. > > I thought I had it with this SQL: > > SELECT InstName from Institutions ORDER BY ltrim (InstName, 'The'); > > Looked good, until I found 'Texas A&M University' sorting below York. > > Seems ltrim() removes inital charaters from the set of charaters, not > inital strings, so I was sorting on 'xas A&M University' > > Anyone have some magic solution for this? > > Ross > -- > Ross J. Reedstrom, Ph.D., > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 >