Skip site navigation (1) Skip section navigation (2)

Re: Sorting router interfaces

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Sorting router interfaces
Date: 2010-11-02 00:23:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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   :)

In response to

pgsql-sql by date

Next:From: Uwe BartelsDate: 2010-11-02 07:54:45
Subject: problem with rules
Previous:From: Filip RembiaƂkowskiDate: 2010-11-01 21:37:54
Subject: Re: Sorting router interfaces

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group