CODEWITHSUNDEEP
mysqlunion
Fredy
Fredy

Reputation: 2910

How to using ORDER BY and LIMIT in UNION mysql query

i have a litte problem, i have a MySQL query code like this:

SELECT a.id, b.name AS itemName, b.slug AS itemSlug FROM table_idx a
INNER JOIN table_product b ON b.id = a.targetid AND a.target='PRODUCT'
WHERE a.memberid=$memberid
UNION
SELECT a.id, c.name AS itemName, c.slug AS itemSlug FROM table_idx a
INNER JOIN table_news c ON c.id = a.targetid AND a.target='NEWS'
WHERE a.memberid=$memberid
UNION
SELECT a.id, d.name AS itemName, d.slug AS itemSlug FROM table_idx a
INNER JOIN table_promo d ON d.id = a.targetid AND a.target='PROMO'
WHERE a.memberid=$memberid

How to add ORDER BY a.id DESC LIMIT 0,10 in query above? Thanks for your help.

Upvotes: 0

Views: 4959

Answers (2)

xdazz
xdazz

Reputation: 160853

Wrap it with another select.

SELECT * FROM (
SELECT a.id, b.name AS itemName, b.slug AS itemSlug FROM table_idx a
INNER JOIN table_product b ON b.id = a.targetid AND a.target='PRODUCT'
WHERE a.memberid=$memberid
UNION
SELECT a.id, c.name AS itemName, c.slug AS itemSlug FROM table_idx a
INNER JOIN table_news c ON c.id = a.targetid AND a.target='NEWS'
WHERE a.memberid=$memberid
UNION
SELECT a.id, d.name AS itemName, d.slug AS itemSlug FROM table_idx a
INNER JOIN table_promo d ON d.id = a.targetid AND a.target='PROMO'
WHERE a.memberid=$memberid
) AS t ORDER BY t.id DESC LIMIT 0,10

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166406

Unless your intensions are to ORDER BY and LIMIT each query befor the UNION, Just add

ORDER BY id DESC LIMIT 0,10

At the bottom of the query.

Upvotes: 3

Related Questions