Page 2 of 2
Re: Selecting unique random combinations from a table
Posted: Fri Jun 04, 2021 10:04 am
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 |
Re: Selecting unique random combinations from a table
Posted: Sun Jun 06, 2021 12:57 am
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
Re: Selecting unique random combinations from a table
Posted: Tue Jun 08, 2021 7:36 am
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.
Re: Selecting unique random combinations from a table
Posted: Tue Jun 08, 2021 9:23 am
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.