Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql order by with union doesn't seem to work

Tags:

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

like image 558
Kelly Elton Avatar asked Dec 30 '11 23:12

Kelly Elton


People also ask

Why does ORDER BY not work with UNION?

Because of the parentheses, it is clear that the ORDER BY belongs to the result of the UNION , not the final SELECT . It is usually 'wrong' to have multiple tables with the same schema. Consider combining those tables into one, perhaps with an extra column to say which it is. Then you can get rid of the UNIONs .

Can ORDER BY be used with UNION?

Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. Let us see an example.

Does UNION all preserve order?

No it does not. SQL tables are inherently unordered. You need to use order by to get things in a desired order.

How do you put an order in UNION query?

As the following query shows, when you include an ORDER BY clause, it must follow the final SELECT statement and use an integer, not an identifier, to refer to the ordering column. Ordering takes place after the set operation is complete.


2 Answers

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
like image 95
Abe Miessler Avatar answered Sep 21 '22 09:09

Abe Miessler


I got solution for this:

SELECT *
FROM (
    (SELECT 1 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE city=".$city." AND region=".$region." AND cantone=".$cantone.")
     UNION
    (SELECT 2 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE region=".$region." AND cantone=".$cantone.")
    union all
    (SELECT 3 as SortRank, uid, title, state, zip, region,cantone FROM company WHERE cantone=".$cantone.")
) As u
GROUP BY uid 
ORDER BY SortRank,state=2, title ASC
LIMIT 0,10

In above query i want result eg. first show all records with city, region and cantone then if city not available then show all records with region and cantone and then all records with cantone of city. So, removing repeating records i used GROUP BY clause, it will sort all records based on query group then all records with state=2.

like image 42
Ashish Patel Avatar answered Sep 21 '22 09:09

Ashish Patel