Solution #1: Using ROW_NUMBER with random.value functions
WITH x AS (
SELECT CASE WHEN sal<=999 THEN '0-999'
WHEN sal<=1999 THEN '1000-1999'
WHEN sal<=2999 THEN '2000-2999'
ELSE '3000+'
END range,
ename, sal
FROM scott.emp
), y AS (
SELECT ename, sal, range,
ROW_NUMBER()OVER(PARTITION BY range
ORDER BY dbms_random.value) rn
FROM x
)
SELECT range, ename, sal
FROM y
WHERE rn<=2
ORDER BY range
RANGE | ENAME | SAL | 0-999 | SMITH | 800 | 0-999 | JAMES | 950 | 1000-1999 | TURNER | 1500 | 1000-1999 | ADAMS | 1100 | 2000-2999 | CLARK | 2450 | 2000-2999 | JONES | 2975 | 3000+ | SCOTT | 3000 | 3000+ | KING | 5000 |
---|
Solution #2: Using DECODE, MAX() KEEP and UNION ALL
WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal, ROWNUM || dbms_random.value rnd
FROM scott.emp
)
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename,
MAX(sal) KEEP(DENSE_RANK FIRST ORDER BY rnd) sal
FROM x
GROUP BY range
UNION ALL
SELECT range, MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename,
MAX(sal) KEEP(DENSE_RANK LAST ORDER BY rnd) sal
FROM x
GROUP BY range
ORDER BY range
RANGE | ENAME | SAL | 0-999 | JAMES | 950 | 0-999 | SMITH | 800 | 1000-1999 | MARTIN | 1250 | 1000-1999 | WARD | 1250 | 2000-2999 | JONES | 2975 | 2000-2999 | BLAKE | 2850 | 3000+ | FORD | 3000 | 3000+ | KING | 5000 |
---|
Solution #3: Using SIN for random value simulation and multi-column UNPIVOT with MAX() KEEP function
WITH x AS (
SELECT DECODE(1, SIGN(999-sal), '0-999', SIGN(1999-sal), '1000-1999',
SIGN(2999-sal), '2000-2999', '3000+') range,
ename, sal,
SIN(ROWNUM*TO_NUMBER(SUBSTR(
extract(second
from current_timestamp),-3))
) rnd
FROM scott.emp
), y AS (
SELECT range, MAX(ename)KEEP(DENSE_RANK FIRST ORDER BY rnd) ename1,
MAX(sal) KEEP(DENSE_RANK FIRST ORDER BY rnd) sal1,
MAX(ename)KEEP(DENSE_RANK LAST ORDER BY rnd) ename2,
MAX(sal) KEEP(DENSE_RANK LAST ORDER BY rnd) sal2
FROM x
GROUP BY range
)
SELECT range, ename, sal
FROM y
UNPIVOT (
(ename, sal) for (t1, t2) in ((ename1,sal1), (ename2,sal2))
)
ORDER BY range
RANGE | ENAME | SAL | 0-999 | JAMES | 950 | 0-999 | SMITH | 800 | 1000-1999 | MILLER | 1300 | 1000-1999 | ALLEN | 1600 | 2000-2999 | JONES | 2975 | 2000-2999 | BLAKE | 2850 | 3000+ | FORD | 3000 | 3000+ | KING | 5000 |
---|