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?

Does this answer your question? [SQL Union All with order by and limit (Postgresql)](https://stackoverflow.com/questions/37352296/sql-union-all-with-order-by-and-limit-postgresql) — forpas, Apr 16 '22 at 09:48

score 1 · Accepted Answer · answered Apr 16 '22 at 09:38

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

answered Apr 16 '22 at 09:38

Tim Biegeleisen

score 0 · Answer 2 · answered Apr 16 '22 at 09:43

0

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

answered Apr 16 '22 at 09:43

Romylussone

2 Answers2