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.