Not able to sample the data in UNION

General
sxm
Fan

Not able to sample the data in UNION

Why do I get error "Top N option not allowed in query connected by set operators"

SELECT TOP 10

A,

B,

C

FROM Table A

WHERE A.status = 'R'

UNION ALL

SELECT TOP 10 

A,

B,

C

FROM Table A

WHERE A.status = 'M'

2 REPLIES
Junior Contributor

Re: Not able to sample the data in UNION

You get this error because TOP/SAMPLE is not allowed in a UNION :-)

But there's a workaround: enclose the selects in Derived Tables:

SELECT * FROM 
(
SELECT TOP 10 A,B,C
FROM Table A
WHERE A.status = 'R'
) AS dt

UNION ALL

SELECT * FROM
(
SELECT TOP 10 A,B,C
FROM Table A
WHERE A.status = 'M'
) AS dt

But this is not true sample, you might use a stratified sampling instead:

SELECT A,B,C
FROM Table A
SAMPLE
WHEN A.status = 'R' THEN 10
WHEN A.status = 'M' THEN 10
END

Dieter

sxm
Fan

Re: Not able to sample the data in UNION

Thank you.