Re: numerical sort on mixed alpha/numeric data

On Wednesday 16 July 2003 3:27 pm, Dmitry Tkach wrote:
> 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

Hi,

thanks for this. I had to alias the sub-select, and the cast from varchar to
int didn't work, below is the working version.

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

Gary

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