Unique is not quite UNIQUE

[dreamquartz]

REFERENCING: viewtopic.php?f=13&t=105230

@eremmel & @chrisb

Now that the Client is getting into Exams again, the Client was eager to use the provided solution by both of you.
Alas, after a couple of days, the Client came back to us with the following comments:
Was it not so that when the "level" is set to 12, based on the pre-determined settings, that there should be only 1 "double" (see below: 1 double.pdf) as a possibility?
-There are instances noticed where there are 2 "doubles" identified, which is not desirable (see below: 2 doubles.pdf).

Should it not be so when the "level" is to <12, based on the pre-determined settings, that there no "double" allowed in the first place?
-When the "level"=11, there is a possibility for 1 double (see below: Level 11.pdf).
-With the "level"<11, there was no instance of a "double" identified after many tests at this point in time.

We have 2 questions:
1. We would like to understand why this is happening?
2. We would like to reduce the "doubles" as much as possible where it is column "S" is concerned.
What was not identified in the original request, is that the following is also the case:
S1=S2
S3=S4
S11=S12

The Client informed us that "S" is going to be extended in the future, so there might be more similarities going to be identified under "S" other than the 3 mentioned above.

Dream

1 double.pdf

(78.62 KiB) Downloaded 234 times

2 doubles.pdf

(80.46 KiB) Downloaded 226 times

level 11.pdf

(69.04 KiB) Downloaded 218 times

[eremmel]

Not sure how you implemented the query at the customer (you might post it here again), but in my approach I read the data in random order from the table. This order determines the search tree that the recursive query follows, in opposite of letting the query return all possible solutions. The latter is extreme slow and unworkable. The implementation that I've still around might return shorter lists than requested.
It looks like that your query returns always the requested level, but might end up with too many doubles.
A possible solution might be to calculate the number of doubles inside the query and reject the query when the number of doubles is > (requested level - unique sets).
This can be done by moving the current query to the WITH section as well and alias it as e.g. tree and use a query as:

Code: Select all

with . . .
select *
  , (select count(*) from (select count(s) from tree group by s having count(s) > 1) X) as "doubles" 
from tree
where  level < :max_level 
  and  (
    :max_level = (SELECT count(DISTINCT s) FROM tree)   -- for small sets
    or similar.c = (SELECT count(DISTINCT s) FROM tree)  -- for large sets
) 

This might result in an empty data set, so then the from / report has to be rerun.

[dreamquartz]

@eremmel.

Thanks for your response.

Interesting....
We decided to keep the maximum @ 12, because this the maximum of the exams possible at one time.
The 12 max is a regulatory restriction.

This has an advantage, because now even from one resultset, multiple exam packages can be comprised., especially when the group size is i.e. 5.
It would be interesting if the User can set the "group size" by manual input beforehand, without accessing the Query manually (and potentially leading to Query corruption).

Will look at it.

The query is one on one reproduced, with only some name changes, to make it easier to read. The table has been expanded to the maximum allowable combinations. Initial and clearly identifiable doubles have been removed beforehand.

Dream.