Re: numerical sort on mixed alpha/numeric data

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