Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: numerical sort on mixed alpha/numeric data
Date: Wed, 16 Jul 2003 12:48:26 +0100
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307161248.26835.gary.stainburn@ringways.co.uk>

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

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=#
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000