CODEWITHSUNDEEP
mysqlsql
user499054
user499054

Reputation:

Getting the next available value in a range

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?

Upvotes: 0

Views: 297

Answers (2)

Levin
Levin

Reputation: 2015

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.

Upvotes: 2

slavoo
slavoo

Reputation: 6086

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

Upvotes: 1

Related Questions