How to display database results from multiple tables in descending order?

Question

I have a keyword search on my site that allows visitors to search for specific things. My msysql db has 15 tables and for the search feature I use a UNION ALL to join them all so visitor can search all the tables simultaneously. The problem is all the results are in ascending order. How do I get the results in descending order.

SELECT *

FROM table1

WHERE keyword LIKE %s OR id LIKE %s

UNION ALL

SELECT *

FROM table2

WHERE keyword LIKE %s OR id LIKE %s

UNION ALL

SELECT *

FROM table3

WHERE keyword LIKE %s OR id LIKE %s

ps

I've tried this and it dosn't work:

SELECT * FROM table1 WHERE keyword LIKE %s OR id LIKE %s ORDER BY id DESC

and I know it's not conventional to use SELECT* with so many tables, but trust me I have my reasons. What i'm not trying to do is wrap all the UNION's inside an outer SELECT

bregent · Accepted Answer

it's written exactly like this for all 15 tables

SELECT *

FROM table1

WHERE keyword LIKE %s OR id LIKE %

Order By id DESC

UNION ALL

SELECT *

FROM table2

WHERE keyword LIKE %s OR id LIKE %

Order By id DESC

Once again, the order by clause can only appear at then end of the last select statement in a UNION query. You have it on all of the select statements.

bregent · Answer

>What i'm not trying to do is wrap all the UNION's inside an outer SELECTWhy not? That's really your only option, other than creating a view or sproc.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded