From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: numerical sort on mixed alpha/numeric data
Date: Wed, 16 Jul 2003 12:48:26 +0100
Message-ID: <200307161248.26835.gary.stainburn@ringways.co.uk> (raw)
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.?
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
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: gary.stainburn@ringways.co.uk
Subject: Re: numerical sort on mixed alpha/numeric data
In-Reply-To: <200307161248.26835.gary.stainburn@ringways.co.uk>
* 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