How to use ORDER BY and LIMIT together with UNION

user_3935035 0
Viewed 56

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?

postgresql
2 Answers

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);
user_18632290

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
user_127015290
Related