CODEWITHSUNDEEP
mysqlsql
superhero
superhero

Reputation: 6482

How to find the next available number in a number sequence with MySQL

In MySQL, I'm performing the following query:

SELECT DISTINCT substring_index(gponport, '/', -1) AS ONU
FROM pon
WHERE he = '10.52.8.5'
AND gponport LIKE '16/1/%'
ORDER BY cast(ONU as unsigned)

Where the format of the gponport is (example): 16/1/5

The output of the query is something like:

+------+
| ONU  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
| 10   |
+------+

I need to retrieve the next onu available in the sequenze. In above example, the expected output is 11.

...but, if the sequence is broken, example:

+------+
| ONU  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 6    |
| 7    |
| 8    |
| 9    |
| 10   |
+------+

Then I need the query to return number 5 instead.

Upvotes: 1

Views: 282

Answers (3)

Maciej Los
Maciej Los

Reputation: 8591

Common Table Expressions (recursive queries) together with COALESCE function will do the trick ;)

CREATE TABLE onu
(
    val INT
);

INSERT INTO onu
VALUES (1), (2), (3),
       (4), (6), (7),
       (8), (9), (10);

WITH RECURSIVE CTE AS
(
  -- initial part
  SELECT MIN(val) startonu, MAX(val)+1 endonu
  FROM onu
  -- recursive part
  UNION ALL
  SELECT startonu +1 AS startonu, endonu
  FROM CTE
  WHERE startonu <= endonu
)
SELECT COALESCE(MIN(c.startonu), MAX(c.startonu)) nextval
FROM CTE c
WHERE NOT EXISTS
  (
    SELECT val
    FROM onu o
    WHERE o.val=c.startonu
  );
-- returns 5

Now, add a value of 5 and try above query again. Then it will return a value of 11 ;)

See: db<>fiddle

[EDIT]

+1 in this line SELECT MIN(val) startonu, MAX(val)+1 endonu is surplus.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If your value always starts with 1, you can use the row_number() solution (mentioned in the other answer) adapted to your query:

SELECT COALESCE(MIN(CASE WHEN seqnum <> onu THEN seqnum END),
                max(onu)
               )
FROM (SELECT p.*,
             ROW_NUMBER() OVER (ORDER BY onu) as seqnum,
             MAX(onu) OVER () as max_onu
      FROM (SELECT DISTINCT CAST(substring_index(gponport, '/', -1) as unsigned) AS ONU
            FROM pon
            WHERE he = '10.52.8.5' AND
                  gponport LIKE '16/1/%'
           ) p
      ) p

Upvotes: 2

Soheil Rahsaz
Soheil Rahsaz

Reputation: 781

I created an example based on your second sample data. Take a look at it and see if it helps you or motivates you to find the perfect solution:

Mysql window functions

CREATE TEMPORARY TABLE onu
(
    val INT
);

INSERT INTO onu
VALUES (1),
       (2),
       (3),
       (4),
       (6),
       (7),
       (8),
       (9),
       (10);


SELECT rnk
FROM (SELECT val, (row_number() OVER (ORDER BY val)) AS rnk FROM onu) a
WHERE rnk != val
LIMIT 1;

result:

5

Upvotes: 1

Related Questions