How do I generate UNIQUE random numbers within a range?

Let’s say for every new entry I make in my app, I want to generate a random 6 digit number for a column.

So for example, if I create one entry, I would get a number like 568392.

HOWEVER, I don’t want 568392 to show up again in that column on the table. I want all the numbers generated to be unique. I tried RANDBETWEEN(100000, 999999) but I don’t think that prevents duplicates. Any help?

Since there is no iteration functionality within expressions, you’ll have to use best-effort to hope to guess an available ID:

ANY(
  LIST(
    RANDBETWEEN(100000, 999999),
    RANDBETWEEN(100000, 999999),
    ...
    RANDBETWEEN(100000, 999999)
  )
  - table[column]
)

Replace table and column with the names of the table and column that contain the existing IDs.

The idea here is to generate a small set of random IDs (LIST(...)) and remove any that are already in use (- table[column]), hopefully leaving at least one available IDs.

4 Likes