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 42C97CC5D6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 16 Jul 2003 14:26:42 +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 34944-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 16 Jul 2003 14:26:47 +0000 (GMT)
Received: from exchange.hq.openratings.com (unknown [141.149.165.226])
	by svr1.postgresql.org (Postfix) with ESMTP id C8547CC634
	for <pgsql-sql@postgresql.org>; Wed, 16 Jul 2003 11:26:21 -0300 (ADT)
Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by
	exchange.hq.openratings.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id 38QM4LPQ; Wed, 16 Jul 2003 10:26:48 -0400
Message-ID: <3F1560CC.9030806@openratings.com>
Date: Wed, 16 Jul 2003 10:27:24 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: numerical sort on mixed alpha/numeric data
References: <200307161248.26835.gary.stainburn@ringways.co.uk>
In-Reply-To: <200307161248.26835.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/200
X-Sequence-Number: 14314

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.?
>  
>
What about

select lnid,lnumber,lncurrent from
(select *, case when lnumber ~ '^[0-9]+'  then lnumber::int else null 
end as number from lnumber)
order by number, lnumber


I hope, it helps...

Dima

>
>nymr=# \d lnumbers
>               Table "lnumbers"
>  Column   |         Type          | Modifiers
>-----------+-----------------------+-----------
> lnid      | integer               | not null
> lnumber   | character varying(10) | not null
> lncurrent | boolean               |
>Primary key: lnumbers_pkey
>Triggers: RI_ConstraintTrigger_7121182
>
>nymr=# select * from lnumbers order by lnumber;
> lnid | lnumber | lncurrent
>------+---------+-----------
>   26 | 08556   | t
>   13 | 08850   | f
>    2 | 2392    | f
>   15 | 24 061  | t
>   12 | 25 278  | f
>    1 | 29      | t
>    5 | 30926   | t
>    3 | 4277    | t
>    7 | 44767   | t
>   21 | 45157   | t
>   13 | 4518    | t
>    6 | 45212   | t
>   16 | 45337   | t
>   23 | 4771    | f
>   19 | 5       | t
>   24 | 55019   | t
>   27 | 59      | f
>   11 | 60007   | t
>    8 | 60532   | t
>   23 | 60800   | t
>   14 | 62005   | t
>   14 | 62012   | f
>   18 | 64360   | f
>    2 | 65894   | t
>   17 | 6619    | t
>   27 | 69023   | t
>    9 | 75014   | t
>   10 | 75029   | t
>   22 | 76079   | t
>    4 | 80135   | t
>   20 | 825     | t
>   18 | 901     | t
>    5 | 926     | f
>   26 | D3723   | f
>   15 | D5061   | t
>   12 | D7628   | t
>   25 | D9009   | t
>   24 | D9019   | f
>(38 rows)
>
>nymr=#
>  
>