Any way to remove the duplicate SELECT statement?

Question

For the sake of brevity, let's assume we have a numbers table with 2 columns: id & number:

CREATE TABLE numbers(
   id INT NOT NULL AUTO_INCREMENT,
   NUMBER INT NOT NULL,
   PRIMARY KEY ( id )
);

I want the number column to auto-increment, but restart from 1 after hitting 100, satisfying the following requirements:

1. If there are no records yet, set number to 1.
2. If there are already records in the table:
   1. If the last record's number is less than 100, set number to that + 1.
   2. If the last record's number is 100 or more, set number to 1.

Here's what I have so far:

INSERT INTO `numbers` (`number`)
VALUES (
    IF(
        IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) > 99,
        1,
        IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) + 1
    )
)

This works, but uses two identical SELECT queries.

Is there any way to remove the duplicate SELECT?

---

P.S. I want to do all this within a query. No triggers or stored procedures.

Answer 1

Try to use modulo % operator

INSERT INTO `numbers` (`number`)
VALUES (
          IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) % 100 + 1
    )

Answer 2

I see. The modulo answer is nice. It feels 'brittle' to me as it would not handle the "100 or more" requirement as stated. It's fine if you change the requirement to "If the last record's number is exactly 100, set number to 1."

To me this is more intuitive:

INSERT INTO numbers (number)
SELECT 
  CASE 
    WHEN number >= 100 THEN 0
    ELSE COALESCE (number, 0)
  END + 1
FROM numbers
ORDER BY id DESC
LIMIT 1

Answer 3

You could use a temp variable.

INSERT INTO `numbers`(`number`) VALUES (
  IF((@a := IFNULL((SELECT number FROM `numbers` AS n ORDER BY id DESC LIMIT 1), 0)) > 99, 1, @a + 1)
);