Thread: numerical sort on mixed alpha/numeric data
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 nulllnumber | character varying(10)| not nulllncurrent | 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
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=# > >
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
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;
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.