Thread: Aggregate Network Address functions?

I have a database of IP addresses, some of which are /8 /16 or /24 as well
as /32

I want to do a look up for the "longest" record matching an IP I enter

So the db may contain

208.253.0.0/16
as well as
208.253.55.0/24
and
208.253.55.61/32

and I want the query result to find the 208.253.61/32 record if it exists,
or the smallest netblock that contains it

I tried

SELECT MAX(ip)
FROM nlist
WHERE ip >> '208.253.55.61';

the error is Unable to select an aggregate function max(inet)

I tried

SELECT MAX(masklen(ip))
FROM nlist
WHERE ip >> '208.253.55.61';

That returns an integer of the largest mask length... such as /24 or /32.

How would I get the other fields associated with the record that has the
largest mask length?

Or, in general, how does one find the record with the largest value in a
column and get all the columns from that particular record?

This seems so basic and simple and yet I cannot think of the answer today.

Thank you,

- April

April L <april@i-netco.com> writes:
> I tried

> SELECT MAX(masklen(ip))
> FROM nlist
> WHERE ip >> '208.253.55.61';

> That returns an integer of the largest mask length... such as /24 or /32.

> How would I get the other fields associated with the record that has the
> largest mask length?

> Or, in general, how does one find the record with the largest value in a
> column and get all the columns from that particular record?

The usual recommendation is

SELECT * FROM nlist
WHERE ip >> '208.253.55.61'
ORDER BY masklen(ip) DESC
LIMIT 1;

I'm not sure what people do in databases that don't have LIMIT; there
doesn't seem to be any nice solution in bog-standard SQL.

            regards, tom lane