Maybe I am slow, or something, but I can't quite follow your problem description. So let me tell you have I read it, and where it seems wrong:

the maximum number of "pkey" cols which share a common "rnd" is 3 - AAA, CCC and DDD

No, AAA, BBB, CCC, DDD all share a common “rnd”. That is four, not three. Admittedly, only one occurrence (rnd=1), but you did not ask for the largest number of occurrences?

which each appear in rnds 1,3,4,5, so four occurrences.

But DDD does not appear with rnd=3. Was that a typo, or I have misunderstood even more?

No, AAA, BBB, CCC, DDD all share a common “rnd”. That is four, not three. Admittedly, only one occurrence (rnd=1), but you did not ask for the largest number of occurrences?

So they do. So the currect first answer returned should be 4 common keys, occuring 1 time.

But DDD does not appear with rnd=3. Was that a typo, or I have misunderstood even more?

Sorry. I must have gone crosseyed. AAA, CCC and DDD all appear in rnds 1, 4 and 5, so three occurrence.

Your data matrix does not fit your description. All 5 rnd values have AAA in line 1.

The first integer is the rnd, so AAA appears as line 1 in rnds 1,2,3,4,5. But I am trying to find the most pkey values which share a common rnd. AAA, BBB, CCC, DDD all share rnd 1 (which I missed stating); AAA, CCC and DDD are all present in rnds 1, 4, and 5. So the first row I wish to have returned should show 4 (for the common keys) and 1 (for the number of times they appears together), followed by 3 and 3 for the three common keys, occurring 3 times.

rnd 4 has pkeys 'AAA','CCC','GGG','DDD'

rnd 5 has pkeys 'AAA','GGG','CCC','DDD'

So order matters, or you'd want these counted as one group appearing twice, correct?

rnd 4 has pkeys 'AAA','CCC','GGG','DDD'

rnd 5 has pkeys 'AAA','GGG','CCC','DDD'

So order matters, or you'd want these counted as one group appearing twice, correct?

Dear me, I had a bad day yesterday! The pkey values can appear in any order in a specific rnd, so these two rows would be counted as 2 occurrences of the combination of 4 pkeys.

I don't fully understand your problem, but it sounds like what you are looking for now is a way to generate all possible lexicographically ordered subsets of your pkeys, so you can then go and join a table of those subsets back to your initial data in some way.

The generation of subsets is relatively simple to do recursively, like so:

Given

drop table if exists t1;
CREATE TABLE t1(rnd integer,line integer,pkey TEXT,primary key(rnd,line));
INSERT INTO t1 VALUES
   (1,1,'AAA'),(1,2,'BBB'),(1,3,'CCC'),(1,4,'DDD'),
   (2,1,'AAA'),(2,2,'EEE'),(2,3,'HHH'),(2,4,'DDD'),
   (3,1,'AAA'),(3,2,'FFF'),(3,3,'CCC'),(3,4,'HHH'),
   (4,1,'AAA'),(4,2,'CCC'),(4,3,'GGG'),(4,4,'DDD'),
   (5,1,'AAA'),(5,2,'GGG'),(5,3,'CCC'),(5,4,'DDD');

Then the following will create for you a table of all subsets of pkeys ordered lexicograpically (I think, anyway - basic test looks about right). I have also included a count of how many elements are in the set.

--I have done this as a table as it has to be queried loads of
--times in the CTE, I've not put any indexes on it as it is very small
--and the CTE seems to run quick enough on your data
drop table if exists ordered_pkeys;
create temporary table ordered_pkeys as SELECT
  pkey,
  row_number() over (order by pkey) as id
from
  (select distinct pkey from t1);

drop view if exists calc_all_subsets;
create temporary view
  calc_all_subsets
as with 
  recurse (curidx, nelems, subset) as (SELECT
    0, 0, ''
  union all SELECT
    r.curidx+1,
    r.nelems + sq.incl,
    case
      when
        length(r.subset) == 0 or length(sq.pkey) == 0
      then
        r.subset || sq.pkey
    else
      printf('%s-%s', r.subset, sq.pkey)
    end
  FROM
    recurse as r
    join (select id, 0 as incl, '' as pkey from ordered_pkeys union all select id, 1, pkey from ordered_pkeys) as sq
      on r.curidx+1 = sq.id
      --avoid nothing+nothing=nothing duplicates                                       
      and r.nelems + sq.incl >= 1
  WHERE
    r.curidx < (select max(id) from ordered_pkeys))
  select nelems, subset from recurse;

drop table if exists pkey_subsets;
create table pkey_subsets as select * from calc_all_subsets;

drop table if exists ordered_pkeys;
drop view if exists calc_all_subsets;

select * from pkey_subsets;

I don't fully understand your problem, but it sounds like what you are looking for now is a way to generate all possible lexicographically ordered subsets of your pkeys, so you can then go and join a table of those subsets back to your initial data in some way.

Thanks for that nice solution, but it is just part of the problem. I need these strings of pkeys to be grouped by rnd, so the result will be all combinations of 1,2,3 and 4 pkeys for each rnd.

My solution, using a series of inner joins, looks ugly but works fine, however takes quite a bit of time on the production tables with 11 pkeys for each rnd. From the 34,000 candidate rows it generates 5.7 million combinations, needing 330Mb on disk (the whole database is a little over 30Mb). But a search for a combination for one single pkey, by restricting the generation of combinations to those for that pkey, the query runs quickly enough. Testing with the pkey which occurs most frequently the number of combinations generated is just 70,000, fast and manageable, and I will use that technique on the web site.

Requests for the arbitary "most common combination of 6" and the like will be done as special request, offline.

Thanks for that nice solution, but it is just part of the problem. I need these strings of pkeys to be grouped by rnd,

I think this is easy enough to do by joining pkey_subsets to t1 with the condition the pkey is in the subset (instr(pkey_subsets, pkey)>0), grouping by the subset and rnd, and then having all the elements of the subset in the rnd (having count(*)=pkey_subsets.nelems)

To be honest it was after this join where I was unable to follow your requirement on exactly what should be counted.

so the result will be all combinations of 1,2,3 and 4 pkeys for each rnd.

The above approach returns all subsets within each rnd, but simply adding a where clause on nelems prior to the join can limit to subsets of max length 4.

however takes quite a bit of time on the production tables with 11 pkeys for each rnd.

Yes, the number of subsets is exponential, you won't have to go too high in n before the problem becomes intractable regardless of approach.

Glad you found a solution anyway.