select - mysql order by with union doesn't seem to work

Question

Here is my query

(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only%" OR `joke` LIKE "%only%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%three%" OR `joke` LIKE "%three%") ORDER BY `ups` DESC,`downs` ASC)
UNION
(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%doors%" OR `joke` LIKE "%doors%") ORDER BY `ups` DESC,`downs` ASC)
 LIMIT 0, 30

For some reason it doesn't seem to order by ups or downs...it just tosses me back the results in the order they are naturally in the database.

When I cut it down to only one query, it works fine, but other than that, it seems to ignore it.

I also don't want to order by the entire results, or I would have put LIMIT 0,30 Order By blah

Answer 1

From MySQL documentation:

... use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.

Basically the only time an ORDER in a union will be useful is if you are using LIMIT as well.

So if you query was like this:

(SELECT * FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") ORDER BY `ups` DESC,`downs` ASC LIMIT 10)
UNION ...

Then you would see the first ten records that would be returned based on that order, but they wouldn't necessarily be displayed in order.

UPDATE:

Try this -

(SELECT *, 1 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only three doors%" OR `joke` LIKE "%only three doors%") )
UNION
(SELECT *, 2 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%only%" OR `joke` LIKE "%only%") )
UNION
(SELECT *, 3 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%three%" OR `joke` LIKE "%three%") )
UNION
(SELECT *, 4 as ob FROM `jokes` WHERE `flags` < 5 AND (`title` LIKE "%doors%" OR `joke` LIKE "%doors%"))
 ORDER BY `ob`, `ups` DESC,`downs` ASC LIMIT 0, 30