Getting the next available value in a range

Question

I have a table containing values like the following:

"things"
+ FirstOctet + SecondOctet +
| 0          | 0           |
| 0          | 1           |
| 1          | 0           |
| 1          | 0           |
+------------+-------------+

Essentially, FirstOctet and SecondOctet contain values that range form 0-255. The rows are in no particular order. I want to query the database to get the next available value within the table, based on the lowest FirstOctet and SecondOctet. There are no duplicate values for FirstOctet AND SecondOctet (if that makes sense).

So, if the table looks something like:

"things"
+ FirstOctet + SecondOctet +
| 1          | 0           |
| 1          | 4           |
| 1          | 1           |
| 1          | 3           |
+------------+-------------+

I'd like it to find that the next available values are FirstOctet=1 and SecondOctet=2.

All I have so far is just a pretty simple query to tally up the first octets, and that have a count under 255.

select 
    *, count(*) as count
from
    things
group by first_octet
having count < 255
order by first_octet

I have no idea where to go from here. I'm thinking that once I find rows where FirstOctet < 255, it MUST have an available row. Where do I go from here?

In general “finding the next available” anything is problematic because something else could change the anything before you get to use it. It is usually better to make your `INSERT` or whatever determine the correct value and use it inside a transaction. Is that what you are trying to do? — Dour High Arch, Oct 24 '14 at 23:06
Just an idea: can't you make a table that has the values 0 through 255, join that table with your current table and do a left/right join to see what values you have left, and then pick the first one from that? — Leon Cullens, Oct 24 '14 at 23:18
Finding the lowest available is all you want? Suppose you had a table of all possible combinations of octets, and could join to that table, and, of the values that weren't there, select the first one that wasn't there, ordered by first_octet, then second_octet? I'm going to try putting together something like that for you, but I'm not familiar with mysql facilities - a mysql guy might give a tidy solution. — Levin Magruder, Oct 25 '14 at 03:53

score 2 · Accepted Answer · answered Oct 25 '14 at 04:28

Working demo: http://sqlfiddle.com/#!2/1e60c8/3

CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;
CREATE VIEW ViewByteValues AS
SELECT b0.b + b1.b*2 + b2.b*4 + b3.b*8 + b4.b*16 + b5.b*32 + b6.b*64 + b7.b*128 as v       FROM
ViewBoolean b0,ViewBoolean b1,ViewBoolean b2,ViewBoolean b3,ViewBoolean b4,ViewBoolean b5,ViewBoolean b6,ViewBoolean b7;

-- above from covus corax, http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql

CREATE VIEW ViewOctetPairs AS
SELECT octet1.v as octet1, octet2.v as octet2
  FROM ViewByteValues AS octet1
CROSS JOIN
      ViewByteValues AS octet2
WHERE octet1.v < 256
AND
octet2.v < 256
;

Create Table things 
(Octet1 INT,
 Octet2 INT)
;

Then the query is

SELECT * FROM ViewOctetPairs as allPairs
LEFT JOIN
   things
ON allPairs.octet1 = things.octet1
AND allPairs.octet2 = things.octet2
WHERE things.octet1 IS NULL
ORDER BY allPairs.octet1 ASC, allPairs.octet2 ASC
LIMIT 1

The idea is - you create a 2 column "View", with every permutation of numbers. Then you join it against "things", selecting only the rows from the View that aren't in "things", then you choose the first one, where first means the lowest available first column, and if there's more than one, the lowest available second column within that column.

score 1 · Answer 2 · answered Oct 24 '14 at 23:45

I try this:

select FirstOctet, SecondOctet + 1

from things t1

where not exists (select * from things t2 where t1.FirstOctet = t2.FirstOctet and
                   t1.SecondOctet + 1 = t2.SecondOctet)
      and SecondOctet + 1 < 255

order by FirstOctet, SecondOctet

limit 1

Sql fiddle

Getting the next available value in a range

2 Answers2