Re: SQL Question - sampling matching rows

[CRPence <CRPbottle@xxxxxxxxx>]

Neill Harper wrote:
> I had a requirement in SQL today that I could not find
> an elegant solution to. <<SNIP>>

  Do the following "how to?" questions reflect what is desired:?
   . "SELECT two random child rows for each parent row"
   . "Produce a /sample/ set of matching rows; i.e. data sampling"

  Is the following a valid rewrite\restatement of the scenario?:

  Given:

   CREATE TABLE A ( KEYA CHAR(10) NOT NULL
                  , PRIMARY KEY (KEYA)      )
   INSERT INTO  A VALUES
                  ('A'),('B'),('C'),('D'),('E'),('F')
   CREATE TABLE B ( FKEYB CHAR(10) NOT NULL
                  , JUNK INT
                  , FOREIGN KEY (FKEYB) REFERENCES A (KEYA) )
   INSERT INTO  B VALUES
                  ('A',1),('A',2),('A',3),('A',4)
                 ,('B',1),('B',2),('B',3),('B',4)
                 ,('C',1),('C',2),('C',3),('C',4)
                 ,('D',1),('D',2),('D',3),('D',4)
                 ,('E',1),('E',2),('E',3),('E',4)
                 ,('F',1),('F',2),('F',3),('F',4)

  Desired:

   In one SQL statement [if possible], I would like....
   For all rows in file A, to return X number of child
   rows from file B where the key relationship matches
   (preferably random, but not an absolute requirement).

   For example, the SQL request to CALL RANDOM_B(X)
   where X=2, should generate a result set like;
   i.e. two random child rows, for each parent row:
                 ('A',1),('A',4)
                 ('B',2),('B',3)
                 ('C',1),('C',2)
                 ('D',3),('D',4)
                 ('E',2),('E',4)
                 ('F',1),('F',2)

  If that describes the scenario, how important is the X rows per 
matching parent key; i.e. can fewer than X be allowed, especially in 
cases where there are not even X rows from which to choose?

Regards, Chuck