[Solved] Selecting unique random combinations from a table

Discuss the database features
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Selecting unique random combinations from a table

Post by eremmel »

@dream, not nice to change the rules during the game, but good that you have been able to implement the check on S.
It is now getting tricky indeed. How dynamic has this all to become. Here is an idea:
You can introduce a CTE in the WITH that counts the number of similarities based on S:

Code: Select all

similarities(c) AS (
  SELECT count(distinct S) AS c
  FROM  PUBLIC.TEST  -- TODO: Here has to go your table name
),
We can use this CTE 'similarities.c' in the join condition on S in the recursive query:

Code: Select all

AND ( (SELECT c FROM similarities) <= Y.level+1 OR instr(Y.s_con, CONCAT('-', X.s, '-')) = 0 )

This is just without any testing, but you might get the idea. So when you query more than the number of similarity sets, you get duplicates in the similarities.

Have fun, and I still expect contributions to the red cross organization for picking my brains..... :)
 Edit: Changed join condition 
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Selecting unique random combinations from a table

Post by dreamquartz »

@eremmel,

Still volunteering at the Red Cross.
Thank you so much. This solution works well.

Last question, by the looks of it.
Is there a way to integrate User input for the number of records?
'level' is no fixed within the 'with'

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Selecting unique random combinations from a table

Post by eremmel »

dreamquartz wrote:...
Last question, by the looks of it.
Is there a way to integrate User input for the number of records?
'level' is no fixed within the 'with'
Not that difficult to add a WHERE-clause? or is it that LO does not allow for user-bind variables in Native-SQL statements?
For the latter, you should have applied in the past the design pattern:
create view of Native SQL and query the view with 'normal' SQL so all capabilities of Base are available.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Selecting unique random combinations from a table

Post by dreamquartz »

Forgot to mention that I found a simple solution.
Work got in the way.

LO does NOT allow User input in Native SQL, as far as I know.
Never could find a method to do so, so this is what I did:

Because the maximum is always 12, as a result of the 12 exams that are available, the 'Level' will remain 12.
By creating a View of the resultset, the user can now enter the requested number, between 1-12, required for the number of exams provided, via a simple query.
The Query is based on the number of Participants in random order, and the resultset of your suggested 'with recursive' query.

Because there will be more exams in the future, it is easy to set the 'Level' to the new number.

Again thank you eremmel and you chrisb. I mark the topic as solved.

Dream

NOTE: I just realized that this solution can be used for selecting the winning LOTTO NUMBERS of the coming lotto.
Good luck everyone, if you play.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply