Re: [SQL] numerical sort on mixed alpha/numeric data

[Dmitry Tkach]

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=#
 



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])