Am 01.11.2010 13:15, schrieb Brian Sherwood:
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
What I get instead is the following text ordering:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/10 | 1/0/10 | {1,0,10}
GigabitEthernet1/0/11 | 1/0/11 | {1,0,11}
GigabitEthernet1/0/12 | 1/0/12 | {1,0,12}
GigabitEthernet1/0/13 | 1/0/13 | {1,0,13}
This was the easy part.
Suppose those lines above were the input table "interfaces" and the
columns were called c1, c2, c3.
Since c3 is allready an array you could do this:
select *
from interfaces
order by (c3::integer[])[1], (c3::integer[])[2], (c3::integer[])[3]
Records of this type
lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
work with:
order by (c3::float[])[1], (c3::float[])[2], (c3::float[])[3]
Now you "just" need to identify those records which wont produce such
nice numerical arrays.
Then split the two sets up, sort them in separate selects, add a set_nr
and a row_number() as row_nr.
Then UNION both sets together again
AND eventually do an ORDER BY set_nr, row_nr
and you are allready done.
OK, that would be just an idea :)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
