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