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
    )