mysql order by with union doesn't seem to work

mysql select sql-order-by union

13,731

Solution 1

... 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

Solution 2

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.

Solution 3

What the query does, is to order each sub-query separately and unifying all of them. There is no guarantee the result would be ordered.

what you need to do is to order the unified query as such:

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

13,731

Author by

Kelly Elton

int main(){return main();}

Updated on June 06, 2022

Comments

Kelly Elton almost 2 years

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

Kelly Elton over 12 years

Do you have any ideas on how to make it work the way that I would like it to? Essentially I want to combine the different queries, and have each one individually sorted by there ups and downs, then combined, one after another.
Kelly Elton over 12 years

So the first query sorted, then the second sorted. If the first is greater than 30, then it would only show the first 30 in the first query.
Kelly Elton over 12 years

I had thought about that, but as I mentioned above, that's not the result i'm looking for. All results from the first query should always come before any from the second.
Abe Miessler over 12 years

Bleh, that's a tough one. You're saying you don't want to sort the entire set correct? Just append one sorted set to the end of another and take the first 30?
Kelly Elton over 12 years

That's correct. There is paging so when someone goes to the next page i'll limit 30,60 etc.
Kelly Elton over 12 years

If I order the whole thing then then entire first query loses it's relevancy
Kelly Elton over 12 years

oo that looks promising, I'll try it out.
Uri Goren over 12 years

I edited the query to fit your needs (added an additional field 'p')
Kelly Elton over 12 years

I'd give it to you, but the other guy had the correct solution first. Thanks though
Kelly Elton over 12 years

The only problem this seems to bring up now is I have duplicate results. So one last question, is there something I can add to the query to get rid of that?
Kelly Elton over 12 years

So just replace UNION with UNION ALL?
cairnz over 12 years

Try it, see if it helps, if not, i wasted not more than 20 seconds of your time :) .. (i don't have a local mysql handy to test it out, but it should make a difference)
Kelly Elton over 12 years

wait, I don't want duplicates.
cairnz over 12 years

Then stick with adding the additional column to sort by, as in the answer already accepted.
Kelly Elton over 12 years

Another thing with this query, surrounding the whole thing in a Select * FROM actually returns no results
Kelly Elton over 12 years

Another thing, ob should be ASC.
Jonathon almost 11 years

That is strange. Why would MySQL every want to randomly order an ordered list when concatenating?
Paul Albert about 7 years

Thank you! I was spending hours trying to figure this out.
that-ben almost 6 years

I was about to thumbs down this answer because under phpMyAdmin with your specific syntax it generates an error while parsing, but to my surprise, when actually executing the query, it works perfectly fine. WTF? So anyway, I thumbed up instead! :)
ToolmakerSteve about 5 years

GROUP BY uid is not guaranteed to return a specific row. You are relying on undocumented behavior that it return the first one in which it appears. That is, there is no guarantee that if a uid appears in both SortRank 1 and SortRank 2, that it will still have SortRank 1 after the group by.