Message-ID: <4CCF59F6.7050409@gmx.net>
Date: Tue, 02 Nov 2010 01:23:18 +0100
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Sorting router interfaces
References: <AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com>
In-Reply-To: <AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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   :)