[SOLVED] ORDER BY NEWID() AND UNION using MS SQL

ORDER BY NEWID() AND UNION using MS SQL

I'm trying to execute following SQL query over MS. SQL, objective is to select random records from both queries and union them together but its generating the error at ORDER BY NEWID()

select 2 random questions when subject_id=1

union

select 3 random questions when subject_id=2

(SELECT TOP 2 REC_ID, REF_ID, SUBJECT_ID, QUESTIONS, ANSWERS 
FROM         dbo.questions WHERE     (SUBJECT_ID= 1) ORDER BY NEWID() ) 
UNION ALL    
(SELECT TOP 3 REC_ID, REF_ID, SUBJECT_ID, QUESTIONS, ANSWERS FROM 
dbo.questions WHERE     (SUBJECT_ID= 2) ORDER BY NEWID() )

Solution

Try this.

SELECT * FROM
(
    SELECT TOP 2 REC_ID, REF_ID, SUBJECT_ID, QUESTIONS, ANSWERS 
    FROM dbo.questions 
    WHERE SUBJECT_ID= 1 
    ORDER BY NEWID()
) A
UNION ALL
SELECT * FROM    
(
    SELECT TOP 3 REC_ID, REF_ID, SUBJECT_ID, QUESTIONS, ANSWERS 
    FROM dbo.questions 
    WHERE SUBJECT_ID= 2
    ORDER BY NEWID()
) B