How to use ORDER BY and LIMIT together with UNION

Question

This works

SELECT * 
    FROM foo
UNION
SELECT *
    FROM bar

This gives me a "syntax error near UNION".

SELECT * 
    FROM foo
    ORDER BY random() LIMIT 1000
UNION
SELECT *
    FROM bar
    ORDER BY random() LIMIT 1000;

Why is that? How to UNION tables with ORDER BY and LIMIT?

Answer 1

You need to place each half of the union into a separate subquery:

(SELECT * FROM foo ORDER BY RANDOM() LIMIT 1000)
UNION ALL
(SELECT * FROM bar ORDER BY RANDOM() LIMIT 1000);

Answer 2

You can try this too, englobe your statement in a Select

SELECT *
FROM (SELECT * FROM foo ORDER BY random() limit 10) as dt1
UNION
SELECT *
FROM (SELECT * FROM bar ORDER BY random() limit 10) as dt2