X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 785DACC6DC for ; Wed, 16 Jul 2003 16:50:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 54792-07 for ; Wed, 16 Jul 2003 16:50:33 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id EDFFDCC6C8 for ; Wed, 16 Jul 2003 13:50:05 -0300 (ADT) Received: (qmail 5540 invoked by uid 500); 16 Jul 2003 16:54:56 -0000 Date: Wed, 16 Jul 2003 11:54:56 -0500 From: Bruno Wolff III To: Gary Stainburn , pgsql-sql@postgresql.org Subject: Re: numerical sort on mixed alpha/numeric data Message-ID: <20030716165456.GE1263@wolff.to> Mail-Followup-To: Gary Stainburn , pgsql-sql@postgresql.org References: <200307161248.26835.gary.stainburn@ringways.co.uk> <20030716164106.GC1263@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030716164106.GC1263@wolff.to> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/206 X-Sequence-Number: 14320 On Wed, Jul 16, 2003 at 11:41:06 -0500, Bruno Wolff III wrote: > On Wed, Jul 16, 2003 at 12:48:26 +0100, > Gary Stainburn wrote: > > Hi folks, > > > > I've got a table holding loco numbers and an id which references the locos > > table. How can I sort this table, so that numeric values appear first in > > numerical order followed by alpha in alpha order.? > > You can first sort by whether or not a pattern match succeeds. You haven't > said whether or not the empty string is a number or an alpha and that will > affect the choice of pattern. > > For example: > select * from lumbers order by lumber !~ '^[0-9]+$', lumber; I missed the need for numeric ordering for the numbers. The CASE solution someone else suggested seems to answer your question though.