Select next or first

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Select next or first

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Maybe an aggregate function such as MIN() would work well?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply