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
midrange.com
