How to create random unique number?

English
#1

I need to create a very small DB with a handful of categories and < 50 entries, each generating a–unique–random small number. I don’t really need a form to create the categories, but it would be nice to have a form to create the entries; for the latter the only thing I need is to select a category then for the DB to give me a random–unique–number, hopefully from 00 to 99. I suspect simply setting the field to be random & unique number to unique then somehow have it be automatically generated to something random wouldn’t be sufficient, that if what’s generated is already assigned it would yield an error and leave the user with little recourse, if so that would require a way to handle such cases, perhaps by either

  1. generating a random number, or
  2. incrementing the number in a manner it would cycle back to the minimum when it reaches the maximum (eg 98, 99, 00, 01, …),
    then trying again to save, and if that fail repeat (whichever #1 or #2 was chosen).

I have no idea how to proceed, would you kindly help with that? I’ve created the example
category_random_number.odb (3.3 KB)
, which the number to be random and unique is the field Label from the table Samples.

Thank you

#2

RAND() returns a random number between 0 and 1 for each selected record. A record selection in HSQL always requires a FROM clause with one or more tables or other selection(s).

SELECT RAND() AS "R" FROM "TABLE" selects as many random numbers as there are records in “TABLE”.
SELECT RAND() AS "R" FROM "TABLE" LIMIT 3 selects 3 random numbers if “TABLE” has that many records.
SELECT * FROM "TABLE" ORDER BY RAND() orders the table rows randomly.
SELECT * FROM "TABLE" WHERE RAND()>0.5 returns a random selection of approximately half the records.

#3

I’d like something like
LO-Base-category_random_number
that I can submit on the fly and very quickly afterward (eg a few ms), a random–unique–number is recorded then displayed; so very soon after choosing a category, the random unique # that was just generated is shown. Hopefully as soon as I click ok on View Sample that New Sample is showed again so I can submit another until I’m finished and close New Sample.

#4

RandByCategory.odb (124.8 KB)

#5

I don’t see how your attachment ensures the number is unique. Also, while I did not mention the requirement, there’s allusion an integer is required both in the OP and the example image; so what I really need is: a random–unique–integer.

#6

The ID number of the data record is a primary key which is unique by definition.

#7

You table Data has for ID 0 to 1000 constantly increasing by 1. I’m not asking for an ID, I’m asking for a random–unique–integer in each tuple. It can be a PK but it doesn’t have to be; the fields can be (names as CSV) : ID, category, random. I’d like every time I create a new row, that 1 of the columns is automatically a new random–unique–integer.

#8

Never heard of a function, which could create a random value and guarantee there isn’t the same value two times in the result.

#9

It would be e.g. a sequence of numbers from 1 to N, and then sorted in a random order. It seems that something in that line was suggested (I didn’t check the attachments, only guess based on the suggestion to sort using RAND()).

#10

So this would be: Create a table with numbers from 0 to 99. Lets call it “Numbers”. And then:

SELECT "Max99", RAND() AS "Sort" FROM "Numbers" ORDER BY "Sort"
#11

How does that insure the number is unique? Wouldn’t it be better to do something like create a table named DataRandom without a PK with a column named Random from 0 to 99, then randomize it, then create a new column from 0 to 99 name ID then it as PK; then when when a row is created from the Data table, get the Random column matching both IDs? I’m not good at SQL, so here’s some made up code that might give you an idea:

create DataRandom column Random from 0 to 99 by 1 as required unique
modify DataRandom randomize all
modify DataRandom add column ID from 0 to 99 by 1 as PK
create Data column ID auto_increase 1 from 0 as PK,
    column Category ref Category.ID where Category.ID = Data.Category,
    column Random auto_filled ref DataRandom.Random where DataRandom.ID = Data.ID

then I just have to create a new Data row with the single argument Category and the row is automatically correctly created?

#12

That’s what the query does. Table without PK, so not changeable in GUI, values from 0 to 99 in column “Max99”. This are unique values. Ordered by RAND(). You will get a randomized sorting of unique values.
category_random_number.odb (5.4 KB)

#13

The button on my should not reload the form, which may repeat the ID. You get a sequence of unique random IDs when you navigate from record to record without reloading. Change the action of that button to “Next record”.

#14

@DynV,
it’s unclear to me how the stored data should be handled.
.
it’s likely that at some point you will wish to clear the data and restart the process.

this is the path I have taken, 100 numbers (0-99) stored in a fixed random order which means that the cycle is repeated after every 100 inserts.
.
I have included a small macro fired by a button which deletes stored data and enables a fresh restart.
Random.odb (17.4 KB)

#15

Thanks again. In case someone else want to use this: The only missing thing is a way to export the data. I got an error containing–a lot–of text when I tried to make a report from qAllSavedData_sql, as a workaround someone can copy the query qAllSavedData_sql (by right-clicking it) then create a new Calc document then paste in cell A1.
@cpb If you want to make a new version including a way to export, I’d be glad to select it as a solution.

#16

@DynV,
if I do everything for you then you won’t have learned anything.
the query “qAllSavedData_sql” filters the data by “Category” and sorts by “ID”.
I don’t know how you wish to view the data, there’s not much of it, you can set grouping and/or sort order in the report itself.
here’s the SQL with the WHERE and ORDER BY clauses removed, unfiltered and unsorted, this should be fine as data-source for report.

select
	"s"."ID" as "SavedDataID", "c"."Name" "Category", "n"."N"
from
(
	select
		"tSavedData".*, mod("ID", 100) "m"
	from
		"tSavedData"
) "s"
join
	"tNumbers" "n" on "s"."m" = "n"."ID"
join
	"tCategories" "c" on "c"."ID" = "s"."CategoryID"
1 Like
#17

The usual way to solve this is creating a view from the query, the using the view for the report.

1 Like
#18

@DynV,
if you wish to retain filtration and sort order then as suggested by Wanderer you could create a view from the query “qAllSavedData_sql” but first replace this:
from "tFilter" where id = 1)
with this:
from "tFilter" where "ID" = 1)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.