[Solved] Rand() a table selection

[dreamquartz]

Hello All,

I would like to be able to select a number of random records of a table to use for further processing.
I have a table tValues containing a set order of records.
I want to, based on the number of people selected, select the same number of records from tValue.
tValue is indexed by ValueID, starting with '0'.
The group of people is constantly changing, based of a selection process.
For every group, I do not want to continuously link tValue.ValueID='0' to the 1st person in the selected group.
This would mean that 2 or more groups of people with the same number of people, always get related to the same values in tValue.

I can easily create a view of tValue, where the index is randomized via RAND().
My problem is now how to link the top value from the randomized table tValue, called: vValueRandom, to relate to the top value of the selected group of people, and the 2nd value of vValueRandom to the 2nd selected person, and so on.

Can someone please provide me with some guidance,

Thanks in advance,

Dream

[F3K Total]

Try attached, see query Q_PEOPLE_VALUES, every reload supplies new random values from T_VALUE

[dreamquartz]

Try attached, see query Q_PEOPLE_VALUES, every reload supplies new random values from T_VALUE

Thank you so much.
This principle is what I was looking for.

I do have 2 questions though.
1. I am using a split database where this principle is to be used, and it appears that I cannot get the views to do what I can do with the views in your example.
When I open your views the information is shown. When I edit the table I see the SQL behind them.
I cannot reproduce that.
I can creates the views I need, but the moment I want to edit them, i see a regular table structure.
When I check the "script" file, i significant differences between my database and your example.
Your views are created "AS SELECT" . In my database I just see the table definitions.

In my database I cannot create a "LEFT or RIGHT JOIN" and save it as an Query with "run SQL command directly".

2. I have developed my own creations, based on your example, and after a while my interpretation of your "V_RAND_VALUE_ID_V" stops becoming random.
Could that have to do with me using a split database?

I have never used the views the way your example shows.

I am missing something here, and do not know what.

Can you share some insight?

Dream

[F3K Total]

What Version of HSQLDB do you use?
If i split the DB i find in the script file:

Code: Select all

CREATE VIEW "V_RAND_VALUES" ("ID_RAND","VALUE") AS SELECT RAND( ) AS "ID_RAND", "VALUE" FROM "T_VALUE" ORDER BY "ID_RAND"
CREATE VIEW "V_RAND_VALUES_HELP" ("ID_RAND","VALUE") AS SELECT "VR1"."ID_RAND", "VALUE" FROM "V_RAND_VALUES" "VR1" LEFT JOIN "V_RAND_VALUES" "VR2" ON "VR1"."ID_RAND" >= "VR2"."ID_RAND"
CREATE VIEW "V_RAND_VALUE_ID_V" (ID_V,"VALUE") AS SELECT COUNT("ID_RAND") as ID_V, "VALUE" FROM "V_RAND_VALUES_HELP" group by VALUE

R

[dreamquartz]

What Version of HSQLDB do you use?
If i split the DB i find in the script file:

Code: Select all

CREATE VIEW "V_RAND_VALUES" ("ID_RAND","VALUE") AS SELECT RAND( ) AS "ID_RAND", "VALUE" FROM "T_VALUE" ORDER BY "ID_RAND"
CREATE VIEW "V_RAND_VALUES_HELP" ("ID_RAND","VALUE") AS SELECT "VR1"."ID_RAND", "VALUE" FROM "V_RAND_VALUES" "VR1" LEFT JOIN "V_RAND_VALUES" "VR2" ON "VR1"."ID_RAND" >= "VR2"."ID_RAND"
CREATE VIEW "V_RAND_VALUE_ID_V" (ID_V,"VALUE") AS SELECT COUNT("ID_RAND") as ID_V, "VALUE" FROM "V_RAND_VALUES_HELP" group by VALUE

R

Using 2.5 and 2.4.2.
Both have the same problem.
So, entering your example into the split db, it leads to normal table info in my script.

Did you create the tables as your example above, or did you use and other method?

Dream

[F3K Total]

If you use a split-database, the GUI of AOO-Base can not longer treat the view correctly. It's shown as a table, you can only modify it, like tables, using Tools/SQL...
To see your VIEW_DEFINITION, just create a query like the following:

Code: Select all

SELECT "VIEW_DEFINITION" FROM "INFORMATION_SCHEMA"."VIEWS" WHERE "TABLE_SCHEMA" = 'PUBLIC'

R

[dreamquartz]

Thanks. I understand that.

Can you shed some light then on your example, running in a split database situation, as I mentioned as question 2. above?
Does that mean then that as a result it runs in a split database, this example will freeze?

Dream

[chrisb]

after downloading & checking out the code from @F3K Total's innovative example db i was surprised that it produced 100% accuracy (and it does).
the reason that i was surprised was because the view which generates the random value is then in a second view self joined & i expected that the random values would be re-generated. using hsqldb 1.8.0.10 that does not happen but using hsqldb 2.5 it does.

Edit: to be clear the code penned by @F3K Total will fail when used with hsql 2x (i tested with hsql 2.5). why? the randomly generated value used to make the self join is regenerated each time the view is called. i.e. the second set of random values will not mirror the first. the code below should be executed using a split version of @F3K Total's db in combination with hsql 2.5.jar

using @F3K Total's db:
paste the code below into the query window, hit 'RunSQL command directly' & then execute. i don't have the time to thoroughly check it so i leave that to you.
note: HSQL 2x split (will not work with embedded db). data sources are the tables "T_PEOPLE" & "T_VALUE" (no views required).

Code: Select all

select p."Name", v.VALUE
from T_PEOPLE p
join
(
	select v.*, rownum()-1 r
	from
	(
		select ID_V, VALUE, rand() rnd
		from
		T_VALUE
		order by rnd
	) v
) v
on v.r=p.ID

[eremmel]

was because the view which generates the random value is then in a second view self joined & i expected that the random values would be re-generated. using hsqldb 1.8.0.10 that does not happen but using hsqldb 2.5 it does.

Just a remark (did not test it), what will happen when you query the view once in the WITH section and use it twice in the SELECT section of a WITH ... SELECT query?

[F3K Total]

Hello chrisb,
thank you for your solution and explanation using rownum().
I tried different other versions to run using HSQL 2.x but i can also say, it did not work.
R

[dreamquartz]

Hi chrisb and F3K Total,

With the method provided by you chrisb, I was able to use that to solve the issues that I had.
It works really well.

Thank both of you so much,

Dream

[chrisb]

the code may be optimised by disposing the field "rnd".

Code: Select all

select p."Name", v.VALUE
from T_PEOPLE p
join
(
	select v.*, rownum()-1 r
	from
	(
		select VALUE 
		from
		T_VALUE
		order by rand()
	) v
) v
on v.r=p.ID