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

Question

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.

Then I need the query to return number 5 instead. And if the sequence have no 1 value then you need this value to be returned, is it? — Akina
– Akina, Commented Aug 25, 2021 at 10:37
I understand that you made sql-related question, but problems like this is much better handled (and controlled) in application layer, e.g. Go/PHP/Nodejs/whatever data programming/processing language you prefer. — TomiL
– TomiL, Commented Aug 25, 2021 at 12:22
@TomiL thanks for sharing, but I disagree on your statement that one or the other is "better". This is not necessarily a heavy query, and as such not necessarily better or worse expressed in one place instead of the other. It's your opinion as I see it, not a fact... — superhero
– superhero, Commented Aug 28, 2021 at 11:52

Gordon Linoff · Accepted Answer · 2021-08-25 11:50:20Z

2

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

edited Aug 25, 2021 at 11:50

answered Aug 25, 2021 at 10:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

superhero Over a year ago

ty for the answer, the first select I noticed an error , I guess the max is expected to be the second select, not the second argument to min. But additionally we can not use OVER, old version of mysql.. legacy :( (1064): Syntax error near ‘(ORDER BY onu) as seqnum, MAX(onu) OVER () as max_onu FROM ’ at line 3 ty though, I will look into it more to see how I can adopt it - if I can adopt it.

Gordon Linoff Over a year ago

@superhero . . . No, it was wishful coding. I left out the COALESCE().

Soheil Rahsaz · Accepted Answer · 2021-08-25 10:06:09Z

1

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:

answered Aug 25, 2021 at 10:06

Soheil Rahsaz

81110 silver badges23 bronze badges

4 Comments

superhero Over a year ago

ty, but this is almost like the legacy solution I'm already using for the past years, I'm trying to find a "one query" solution for a refactor. still +1 though, it does solve the issue.

Gordon Linoff Over a year ago

@superhero . . . This doesn't return 11 if there are no gaps.

Soheil Rahsaz Over a year ago

Yes as I said it's not the perfect answer and only works with the second sample. Just to give you some idea

superhero Over a year ago

I think Gordon referred to what I wrote, that it "does solve the issue". It's a mistake by me to say it does, but I agree with the author, this example serves a purpose, I should have written what I'm already using, but that's a very ... messy ... piece of query :)

Maciej Los · Accepted Answer · 2021-08-25 12:16:19Z

1

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.

edited Aug 25, 2021 at 12:16

answered Aug 25, 2021 at 12:10

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

1 Comment

superhero Over a year ago

Have not tried this yet, already implemented a solution in code to be able to move on, but this looks like what I'm looking for. Just worried about possible legacy issues I'm facing with what we are discussing here.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related