Ordering results in a union

[fileserverdirect]

Hello, I am currently using UNION to join two tables for a query, however the problem is that I would like all of the results to be ordered based on a shared column. Basically, right now each Selection query is being ordered by a integer, however when they are combined, all of the order is mixed up. Here is my sql:

(SELECT title, description, author, featured, 'snippet' AS tablename FROM snippet WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ") UNION (SELECT title, description, author, featured, 'script' AS tablename FROM script WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ")

I cannot use array_sort in php because it goes through a while loop to display the results. Any suggestions?

[james438]

(SELECT title, description, author, featured, 'snippet' AS tablename 
FROM snippet WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ") 
UNION 
(SELECT title, description, author, featured, 'script' AS tablename 
FROM script WHERE featured > 0 ORDER BY `featured` LIMIT " . $p3['limit'] . ")
ORDER BY featured

As you can see, if you want to sort all of the tables by a shared column such as 'featured' list the command after the last SELECT statement and after the closing parentheses.

[fileserverdirect]

What a belated response, I solved this problem 4 months ago btw. But thanks anyways! How did you manage to find this thread anyways?

[james438]

Yes, it is a very late response. I felt like working on a few coding puzzles, so I am just browsing through the MySQL history pages and answering ones that have not been answered and look interesting. Considering that people post so seldom in this MySQL forum your thread was only on like page 2.

If nothing else, it is fun for me and it also may help someone who is browsing through the forum with a similar problem. Every once in a while I like to binge code like this.