SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-16
Examples of SAMPLE
•
The BALANCE option enables stratified sampling. Retrieve the age and salary of
1000 sales people such that 50 percent of the result are male and 50 percent
female.
SELECT age, sex, salary
FROM salesperson
SAMPLE FIRST
BALANCE WHEN sex = 'male' THEN 15 ROWS
WHEN sex = 'female' THEN 15 ROWS
END
ORDER BY age;
AGE SEX SALARY
----------- ------ -----------
22 male 28000.00
22 male 90000.00
22 female 136000.00
22 male 37000.40
... ... ...
--- 30 row(s) selected.
•
Retrieve all sales records with the amount exceeding $10000 and a random
sample of 10 percent of the remaining records:
SELECT *
FROM sales
SAMPLE RANDOM
BALANCE WHEN amount > 10000 THEN 100 PERCENT
ELSE 10 PERCENT
END;
EMPID PRODUCT REGION AMOUNT
----- -------------------- ------ -----------
1 PCGOLD, 30MB E 30000.00
23 PCDIAMOND, 60MB W 40000.00
29 GRAPHICPRINTER, M1 N 11000.00
32 GRAPHICPRINTER, M2 S 15000.00
... ... ... ...
228 MONITORCOLOR, M2 N 10500.00
... ... ... ...
--- 32 row(s) selected.