MySQL - Using UNION with LIMIT

sql mysql mysql-error-1064
iceangel89 picture iceangel89 · Sep 27, 2009 · Viewed 7.2k times · Source

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

Asaph picture Asaph · Sep 27, 2009

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.

Related questions

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO

I've looked through all the other StackOverflow (and google) posts with the same problem, but none seemed to address my problem. I am using PDO and PHP. My code: $vals = array( ':from' => $email, ':to' => $recipient, ':…

Read More
How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id …

Read More
MySql Inner Join with WHERE clause

Here is my code: SELECT table1.f_id FROM table1 WHERE table1.f_com_id = '430' AND table1.f_status = 'Submitted' INNER JOIN table2 ON table2.f_id = table1.f_id where table2.f_type = 'InProcess' I need information …

Read More