From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Sorting router interfaces
Date: Tue, 02 Nov 2010 01:23:18 +0100
Message-ID: <4CCF59F6.7050409@gmx.net> (raw)
In-Reply-To: <AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com>
References: <AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com>
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 :)
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: maps.on@gmx.net
Subject: Re: Sorting router interfaces
In-Reply-To: <4CCF59F6.7050409@gmx.net>
* 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