RE: SQL Question - sampling matching rows

["Neill Harper" <neill.harper@xxxxxxxx>]

This is exactly correct, it is acceptable for the number of rows returned
per key code to be less than x, if there are not x rows to return. 

Cheers

Neill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: 03 September 2009 01:53
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Question - sampling matching rows

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