MySQL - Using UNION with LIMIT

Question

i noticed that

(SELECT title, relavency, 'search1' as source FROM search1
ORDER BY relavency DESC
LIMIT 10)
UNION 
(SELECT title, relavency, 'search2' as source FROM search2
ORDER BY relavency DESC
LIMIT 10)
ORDER BY relavency DESC 
LIMIT 10

the last LIMIT 10 does not work. why?

the error was

"Error code 1064: ... to use near 'LIMIT 1000'"

how come mysql workbench detects LIMIT 10 as LIMIT 1000, but if its 1000 it shld work still?

Answer 1

Your query can be rewritten using aliased nested subqueries. This should work for you:

SELECT u.* FROM (
    (SELECT s1.title, s1.relavency, 'search1' as source FROM search1 AS s1
    ORDER BY s1.relavency DESC
    LIMIT 10)
        UNION 
    (SELECT s2.title, s2.relavency, 'search2' as source FROM search2 AS s2
    ORDER BY s2.relavency DESC
    LIMIT 10)
) AS u ORDER BY u.relavency DESC 
LIMIT 10

FYI: you misspelled "relevancy" but I preserved the misspelling so the query would work.