Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Dmitry Tkach <dmitry@openratings.com>
Subject: Re: numerical sort on mixed alpha/numeric data
Date: Wed, 16 Jul 2003 15:39:14 +0100
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200307161248.26835.gary.stainburn@ringways.co.uk>
	<3F1560CC.9030806@openratings.com>
In-Reply-To: <3F1560CC.9030806@openratings.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307161539.14131.gary.stainburn@ringways.co.uk>

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