Page 1 of 1
Select next or first
Posted: Tue Apr 10, 2007 3:18 pm
by pickle
I'm trying to write 1 query that will return me the next available IP, or the first IP if nothing is available.
My table is set up like so:
Code: Select all
+--------------+
| ip |
+--------------+
| XXX.185.3.1 |
| XXX.185.3.31 |
| XXX.185.3.32 |
+--------------+
So if I ask for the next ip after XXX.185.3.1, it should return me XXX.185.3.31. If I ask for the next ip after XXX.185.3.32, it should return XXX.185.3.1.
I haven't really gotten too far with this, as I'm not even sure where to begin. I'm thinking using an IF statement or a sub-query might be helpful, but I'm kind of lost. I'm using MySQL 5.0.18.
Thanks for any and all insight.
Posted: Tue Apr 10, 2007 3:34 pm
by feyd
Code: Select all
SELECT ip FROM tableName WHERE id > 'yourIPstring' ORDER BY ip ASC LIMIT 1
If your query returns zero records, use the first IP.
Posted: Tue Apr 10, 2007 4:24 pm
by pickle
Right - but then that's 2 separate queries I have to do. I'm assuming here I don't necessarily know what the first IP will be.
I've come up with this:
Code: Select all
SELECT IF(
(SELECT ip FROM switches WHERE ip > 'XXX.185.3.32'),
(SELECT ip FROM switches where ip > 'XXX.185.3.32'),
(SELECT ip FROM switches order by ip asc limit 1)
);
Which I think you will agree is dirty, ugly & a bunch of other bad things, but it's all I've been able to come up with.
I'm not sure about the efficiency of doing multiple sub-queries as opposed to multiple individual queries - but using sub-queries can't be all that efficient.
Posted: Tue Apr 10, 2007 4:44 pm
by feyd
Maybe an aggregate function such as MIN() would work well?
Posted: Tue Apr 10, 2007 4:53 pm
by pickle
MAX() would work better, as the minimum value would be the default value & only want that returned if nothing else was found.
I've come up with this, which in my opinion is much nicer:
Code: Select all
SELECT
max(`ip`) AS `next_ip`
FROM
`switches`
WHERE
`ip` > 'XXX.185.3.32' OR
`ip` = (SELECT MIN(`ip`) FROM `switches`)
Edit: Except it doesn't quite work

. The MAX() function is always returning the highest value. So for XXX.185.3.1 and XXX.185.3.31, this query will return XXX.185.3.32. I think I need the MAX() in there, because the ips are not in numerical order. Without the MAX(), it always returns the result from the sub-query.