pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednumerical sort on mixed alpha/numeric data
5+ messages / 3 participants
[nested] [flat]
* numerical sort on mixed alpha/numeric data
@ 2003-07-16 11:48 Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-16 14:27 ` Re: numerical sort on mixed alpha/numeric data Dmitry Tkach <dmitry@openratings.com>
2003-07-16 16:41 ` Re: numerical sort on mixed alpha/numeric data Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2003-07-16 11:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: numerical sort on mixed alpha/numeric data
2003-07-16 11:48 numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-07-16 14:27 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-16 14:39 ` Re: numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 5+ messages in thread
From: Dmitry Tkach @ 2003-07-16 14:27 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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=#
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: numerical sort on mixed alpha/numeric data
2003-07-16 11:48 numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-16 14:27 ` Re: numerical sort on mixed alpha/numeric data Dmitry Tkach <dmitry@openratings.com>
@ 2003-07-16 14:39 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2003-07-16 14:39 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: numerical sort on mixed alpha/numeric data
2003-07-16 11:48 numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-07-16 16:41 ` Bruno Wolff III <bruno@wolff.to>
2003-07-16 16:54 ` Re: numerical sort on mixed alpha/numeric data Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2003-07-16 16:41 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Wed, Jul 16, 2003 at 12:48:26 +0100,
Gary Stainburn <gary.stainburn@ringways.co.uk> 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.?
You can first sort by whether or not a pattern match succeeds. You haven't
said whether or not the empty string is a number or an alpha and that will
affect the choice of pattern.
For example:
select * from lumbers order by lumber !~ '^[0-9]+$', lumber;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: numerical sort on mixed alpha/numeric data
2003-07-16 11:48 numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-16 16:41 ` Re: numerical sort on mixed alpha/numeric data Bruno Wolff III <bruno@wolff.to>
@ 2003-07-16 16:54 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2003-07-16 16:54 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
On Wed, Jul 16, 2003 at 11:41:06 -0500,
Bruno Wolff III <bruno@wolff.to> wrote:
> On Wed, Jul 16, 2003 at 12:48:26 +0100,
> Gary Stainburn <gary.stainburn@ringways.co.uk> 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.?
>
> You can first sort by whether or not a pattern match succeeds. You haven't
> said whether or not the empty string is a number or an alpha and that will
> affect the choice of pattern.
>
> For example:
> select * from lumbers order by lumber !~ '^[0-9]+$', lumber;
I missed the need for numeric ordering for the numbers. The CASE solution
someone else suggested seems to answer your question though.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-16 16:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-16 11:48 numerical sort on mixed alpha/numeric data Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-16 14:27 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-16 14:39 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-07-16 16:41 ` Bruno Wolff III <bruno@wolff.to>
2003-07-16 16:54 ` Bruno Wolff III <bruno@wolff.to>
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