From: Dmitry Tkach <dmitry@openratings.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: numerical sort on mixed alpha/numeric data
Date: Wed, 16 Jul 2003 10:27:24 -0400
Message-ID: <3F1560CC.9030806@openratings.com> (raw)
In-Reply-To: <200307161248.26835.gary.stainburn@ringways.co.uk>
References: <200307161248.26835.gary.stainburn@ringways.co.uk>
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=#
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, gary.stainburn@ringways.co.uk
Subject: Re: numerical sort on mixed alpha/numeric data
In-Reply-To: <3F1560CC.9030806@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox