Db2 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
    id     BIGINT,
    color  VARCHAR(10),
    food   VARCHAR(10),
    sport  VARCHAR(12),
    animal VARCHAR(10)
) DISTRIBUTE ON (id);
INSERT INTO mytable (id, color, food, sport, animal) VALUES
( 1, 'red',    'pizza',  'soccer',     'dog'),
( 2, 'blue',   'sushi',  'tennis',     'cat'),
( 3, 'green',  'tacos',  'basketball', 'horse'),
( 4, 'yellow', 'pasta',  'golf',       'rabbit'),
( 5, 'pink',   'pizza',  'swimming',   'fox'),
( 6, 'orange', 'burger', 'soccer',     'bear'),
( 7, 'black',  'ramen',  'hockey',     'wolf'),
( 8, 'purple', 'tacos',  'cricket',    'cat'),
( 9, 'white',  'salad',  'tennis',     'dog'),
(10, 'brown',  'pizza',  'running',    'deer'),
(11, 'cyan',   'curry',  'boxing',     'tiger'),
(12, 'gray',   'sushi',  'soccer',     'horse'),
(13, 'teal',   'burger', 'baseball',   'owl'),
(14, 'maroon', 'pasta',  'golf',       'dog'),
(15, 'navy',   'tacos',  'rugby',      'frog');
CREATE TABLE numbers (
  id BIGINT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO
  numbers
WITH
  n(d, base, ix, val) AS
(
  VALUES
    (0, 0, 0, 0), 
    (1, 1, 0, 1)

  UNION ALL

  SELECT
    d+1, base*2, ix, base*2 + ix
  FROM
  (
    SELECT
      d, base, ix
    FROM
      n
  
    UNION ALL
  
    SELECT
      d, base, base + ix
    FROM
      n
  )
    AS double_up(d, base, ix)
  WHERE
    d BETWEEN 1 AND 15
)
SELECT
  val
FROM
  n
;
SELECT *
FROM numbers
ORDER BY id DESC
LIMIT 10


ID


65535

65534

65533

65532

65531

65530

65529

65528

65527

65526


WITH
  sampler AS
(
  SELECT
    s.id AS set_id,
    n.id AS sample_id,
    FLOOR(
      RANDOM()
      *
      (SELECT COUNT(*) FROM mytable)
      +
      1
    )
      AS sample_rn
  FROM
    numbers AS s
  CROSS JOIN
    numbers AS n
  WHERE
    s.id < 2
    AND
    n.id < 20
), 
  sorted AS
(
  SELECT
    ROW_NUMBER() OVER () AS rn,
    mytable.*
  FROM
    mytable
)
SELECT
  sampler.set_id,
  sampler.sample_id,
  original.id AS original_id,
  original.color,
  original.food,
  original.sport,
  original.animal
FROM
  sampler
INNER JOIN
  sorted AS original
    ON original.rn = sampler.sample_rn
ORDER BY
  sampler.set_id,
  sampler.sample_id
;


SET_ID
SAMPLE_ID
ORIGINAL_ID
COLOR
FOOD
SPORT
ANIMAL


0
0
4
yellow
pasta
golf
rabbit

0
1
7
black
ramen
hockey
wolf

0
2
4
yellow
pasta
golf
rabbit

0
3
10
brown
pizza
running
deer

0
4
4
yellow
pasta
golf
rabbit

0
5
1
red
pizza
soccer
dog

0
6
11
cyan
curry
boxing
tiger

0
7
14
maroon
pasta
golf
dog

0
8
12
gray
sushi
soccer
horse

0
9
11
cyan
curry
boxing
tiger

0
10
8
purple
tacos
cricket
cat

0
11
10
brown
pizza
running
deer

0
12
10
brown
pizza
running
deer

0
13
1
red
pizza
soccer
dog

0
14
1
red
pizza
soccer
dog

0
15
12
gray
sushi
soccer
horse

0
16
8
purple
tacos
cricket
cat

0
17
3
green
tacos
basketball
horse

0
18
12
gray
sushi
soccer
horse

0
19
15
navy
tacos
rugby
frog

1
0
3
green
tacos
basketball
horse

1
1
3
green
tacos
basketball
horse

1
2
8
purple
tacos
cricket
cat

1
3
8
purple
tacos
cricket
cat

1
4
11
cyan
curry
boxing
tiger

1
5
15
navy
tacos
rugby
frog

1
6
15
navy
tacos
rugby
frog

1
7
9
white
salad
tennis
dog

1
8
10
brown
pizza
running
deer

1
9
7
black
ramen
hockey
wolf

1
10
10
brown
pizza
running
deer

1
11
8
purple
tacos
cricket
cat

1
12
9
white
salad
tennis
dog

1
13
10
brown
pizza
running
deer

1
14
3
green
tacos
basketball
horse

1
15
11
cyan
curry
boxing
tiger

1
16
7
black
ramen
hockey
wolf

1
17
9
white
salad
tennis
dog

1
18
13
teal
burger
baseball
owl

1
19
11
cyan
curry
boxing
tiger


-- test if rand() ever returns 1.0
-- > online docs say it can
-- > this test doesn't seem to show that
WITH
  n AS
(
  SELECT
    INT(RAND()*10) AS val
  FROM
    numbers
)
SELECT val, count(*)
FROM n
GROUP BY val
ORDER BY 1 DESC;


VAL
2


9
6576

8
6352

7
6592

6
6654

5
6645

4
6535

3
6517

2
6616

1
6554

0
6495

SET_ID	SAMPLE_ID	ORIGINAL_ID	COLOR	FOOD	SPORT	ANIMAL
0	0	4	yellow	pasta	golf	rabbit
0	1	7	black	ramen	hockey	wolf
0	2	4	yellow	pasta	golf	rabbit
0	3	10	brown	pizza	running	deer
0	4	4	yellow	pasta	golf	rabbit
0	5	1	red	pizza	soccer	dog
0	6	11	cyan	curry	boxing	tiger
0	7	14	maroon	pasta	golf	dog
0	8	12	gray	sushi	soccer	horse
0	9	11	cyan	curry	boxing	tiger
0	10	8	purple	tacos	cricket	cat
0	11	10	brown	pizza	running	deer
0	12	10	brown	pizza	running	deer
0	13	1	red	pizza	soccer	dog
0	14	1	red	pizza	soccer	dog
0	15	12	gray	sushi	soccer	horse
0	16	8	purple	tacos	cricket	cat
0	17	3	green	tacos	basketball	horse
0	18	12	gray	sushi	soccer	horse
0	19	15	navy	tacos	rugby	frog
1	0	3	green	tacos	basketball	horse
1	1	3	green	tacos	basketball	horse
1	2	8	purple	tacos	cricket	cat
1	3	8	purple	tacos	cricket	cat
1	4	11	cyan	curry	boxing	tiger
1	5	15	navy	tacos	rugby	frog
1	6	15	navy	tacos	rugby	frog
1	7	9	white	salad	tennis	dog
1	8	10	brown	pizza	running	deer
1	9	7	black	ramen	hockey	wolf
1	10	10	brown	pizza	running	deer
1	11	8	purple	tacos	cricket	cat
1	12	9	white	salad	tennis	dog
1	13	10	brown	pizza	running	deer
1	14	3	green	tacos	basketball	horse
1	15	11	cyan	curry	boxing	tiger
1	16	7	black	ramen	hockey	wolf
1	17	9	white	salad	tennis	dog
1	18	13	teal	burger	baseball	owl
1	19	11	cyan	curry	boxing	tiger