0

hi,

i have the following table:

+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| IpAddressesID | int(11) | NO | PRI | NULL | auto_increment |
| PhysicalServerID | int(11) | NO | | NULL | |
| VmID | int(11) | NO | | NULL | |
| IpNetworkID | int(11) | NO | | NULL | |
| IpAddressDigit | varchar(45) | NO | | NULL | |
+------------------+-------------+------+-----+---------+----------------+

If I have into the table the following for IpAddressDigit:

row1 - 9
row2 - 10
row3 - 15

For the next select I want to have 11 as the next digit available not 16.

For now I import all data into php and do computations but It can be done by mysql.

thanks

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by silviud
0
select min(a.IpAddressDigit +1) free_ip 
from iptable a 
left outer join iptable b on b.IpAddressDigit =a.IpAddressDigit +1
where   b.IpAddressDigit  is null
order by a.IpAddressDigit
0

another alternative for mysql

select min(row) from (
SELECT @row := @row + 1 as row,t.IpAddressDigit
FROM iptable t, (SELECT @row := 0) r
order by t.IpAddressDigit 
)a where a.row<>a.IpAddressDigit ;
0

thanks for answers - the first one works fine.
the second returns

+----------+
| min(row) |
+----------+
| 1 |
+----------+

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.