Thread: sorting on inet type?
I have a table with an inet column, which I entered in order initially,
so they came out like:
1.2.3.4
1.2.3.5
1.2.3.40
1.2.3.41
After updating, say, 1.2.3.5, it moves to the end. I understand why,
that's not the problem. Adding an 'ORDER BY host(ip)' clause causes the
ordering to be:
1.2.3.4
1.2.3.40
1.2.3.41
1.2.3.5
Is there an easy way to return to the original order, or do I need to
write a custom PL/SQL function to parse the octets and do sorting
manually? If the latter is the case, help/hints would be appreciated.
Thanks.
P.S. Thanks to the group for all the help you've given on my previous
questions!
--
Dan
____ Kressin ____ .-----------------------------------.
/___ \____________/ __ \ | Unix SysAdmin |
\ \ / / \ | | Global Crossing |
___/ __\/\/rench_ \__/ | | dkressin@globalcrossing.com |
\____/ \____/ | http://www.vib.org/wrench/ |
`-----------------------------------'
I believe this is fixed in 7.1. LER >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 3/20/01, 11:16:42 AM, Daniel "J." Kressin <dkressin@globalcrossing.com> wrote regarding [GENERAL] sorting on inet type?: > I have a table with an inet column, which I entered in order initially, > so they came out like: > 1.2.3.4 > 1.2.3.5 > 1.2.3.40 > 1.2.3.41 > After updating, say, 1.2.3.5, it moves to the end. I understand why, > that's not the problem. Adding an 'ORDER BY host(ip)' clause causes the > ordering to be: > 1.2.3.4 > 1.2.3.40 > 1.2.3.41 > 1.2.3.5 > Is there an easy way to return to the original order, or do I need to > write a custom PL/SQL function to parse the octets and do sorting > manually? If the latter is the case, help/hints would be appreciated. > Thanks. > P.S. Thanks to the group for all the help you've given on my previous > questions! > -- > Dan > ____ Kressin ____ .-----------------------------------. > /___ \____________/ __ \ | Unix SysAdmin | > \ \ / / \ | | Global Crossing | > ___/ __\/\/rench_ \__/ | | dkressin@globalcrossing.com | > \____/ \____/ | http://www.vib.org/wrench/ | > `-----------------------------------' > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > http://www.postgresql.org/search.mpl
Larry Rosenman wrote:
>
> I believe this is fixed in 7.1.
>
> LER
Is there a workaround for 7.0.x? Short of adding extra columns to store
the separate octets and sorting on them?
--
Dan
____ Kressin ____ .-----------------------------------.
/___ \____________/ __ \ | Unix SysAdmin |
\ \ / / \ | | Global Crossing |
___/ __\/\/rench_ \__/ | | dkressin@globalcrossing.com |
\____/ \____/ | http://www.vib.org/wrench/ |
`-----------------------------------'
"Daniel J. Kressin" <dkressin@globalcrossing.com> writes:
> Adding an 'ORDER BY host(ip)' clause causes the
> ordering to be:
> 1.2.3.4
> 1.2.3.40
> 1.2.3.41
> 1.2.3.5
host(ip) produces a text result, so the above sort is according to
string-comparison rules. If you want a numeric sort why don't
you just "ORDER BY ip"?
regards, tom lane
"Daniel J. Kressin" <dkressin@globalcrossing.com> writes:
> Tom Lane wrote:
>> host(ip) produces a text result, so the above sort is according to
>> string-comparison rules. If you want a numeric sort why don't
>> you just "ORDER BY ip"?
> "ORDER BY ip" didn't work. I'm assuming this is the broken behaviour
> that Larry mentioned is fixed in 7.1.
Ah. Sorry, I tried it in current sources to verify that it produced
the sort order you wanted ... but I forgot that we'd done some work
on inet since 7.0.*.
regards, tom lane