Why ORDER BY is being lost in case of UNION

Hi,

We have 2 SELECT that are being joined via UNION.
The code being like this:

(SELECT x, y, z
FROM swd, swv
WHERE swv .keyword = ‘$find’
AND likes > 0
AND swv .meta_id = swd.id
AND display = ‘yes’
ORDER BY likes DESC)
UNION
(SELECT x, y, z
FROM swd
WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST(‘$find’)
AND display = ‘yes’
LIMIT $start, $page)

So, when the 1st SELECT is run by itself it does correctly return the results ORDERed BY number of likes DECS

However, when the 2 SELECTS are run together in the UNION, the ORDER BY DESC of the 1st SELECT is lost!
What is going on?
What do we need to do to maintain ORDER BY DESC when the 2 SELECTS are in UNION?

ThanX
Dean

UNIONS cause orders to be lost as it does a match up. You can fake it by adding a bogus limit to the union, then order the whole result.


SELECT x
	 , y
	 , z
  FROM (SELECT x
			 , y
			 , z
			 , limit
		  FROM swd
		  JOIN swv ON swv.meta_id = swd.id AND swv.keyword = '$find'
		 WHERE likes > 0
		   AND display = 'yes'
		 UNION
		SELECT x
			 , y
			 , z
			 , -1 AS limit
		  FROM swd
		 WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
		   AND display = 'yes' 
		 LIMIT $start, $page) sq
  ORDER BY limit DESC	

simple – an ORDER BY is valid only on the results of the union, not on the individual subqueries

did you know that when you specify UNION without the ALL keyword, the database has to take the entire combined result set and sort it into sequence by all the columns in the SELECT clauses?

it does this so that it can detect and remove duplicate result rows

so then if you have an ORDER BY clause, the entire result set, minus duplicates, is then sorted all over again

Hi,

So what do you suggest?
That we do not use UNION and instead 1st we generate the list returned by the 1st SELECT and then the list by the 2nd SELECT?

depends on what you are trying to achieve and why

did you try the solution in post #2?

DaveMaxwell ,

I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
So here is the real code, can you please give your suggestion to correct this lost ORDER BY problem to the real code, here:

(SELECT swd.id, ix_id, title, descpt, category 
		FROM swd, swv 
		WHERE swv.keyword = '$find' 
		AND swv.vote_up > 0 
		AND swv.meta_id = swd.id 
		AND swd.display = 'yes' 
		ORDER BY swv.vote_up DESC) 
		UNION
		(SELECT id, ix_id, title, descpt, category 
		FROM swd 
		WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
		AND display = 'yes' 
		LIMIT $start, $page)

Thanks.
Dean

All I did was reformat your query to use the join syntax - which the dbms will do anyways, so my way is slightly more efficient. But essentially what I did was make your query a sub-query (except for the order in the first query because it’s redundant due to the UNION) I take the result set from the UNION and sort it.


SELECT swd.id
     , ix_id
	 , title
	 , descpt
	 , category 		
  FROM (SELECT swd.id
			 , ix_id
			 , title
			 , descpt
			 , category
			 , swv.vote_up
		  FROM swd
		 INNER JOIN swv ON swv.meta_id = swd.id 
					   AND swv.vote > 0
		 WHERE swv.keyword = '$find' 
		   AND swd.display = 'yes' 
		 UNION
		SELECT id
			 , ix_id
			 , title
			 , descpt
			 , category 
			 , -1 AS vote_up
		  FROM swd 
		 WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
		   AND display = 'yes' 
		 LIMIT $start
			 , $page) SQ
ORDER BY swv.vote_up DESC 

Or if you want to see it without optimizing yours


SELECT swd.id, ix_id, title, descpt, category 		
  FROM ((SELECT swd.id, ix_id, title, descpt, category 
		   FROM swd, swv 
		  WHERE swv.keyword = '$find' 
		    AND swv.vote_up > 0 
		    AND swv.meta_id = swd.id 
		    AND swd.display = 'yes' 
		  ORDER BY swv.vote_up DESC) 
		UNION
		(SELECT id, ix_id, title, descpt, category 
		   FROM swd 
		  WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
		    AND display = 'yes' 
		  LIMIT $start, $page) SQ
ORDER BY swv.vote_up DESC 

doesn’t this say something about the wisdom of posting fake code

and no, that wasn’t a question

These suggestions do not work, as I suspected since the
swv.vote_up
only exists in the 1st Table. That is why we were doing the UNION to start with since one Table is based on vote_up
but not the other Table.

FYI, MySQL gives Error message:

Table ‘swv’ from one of the SELECTs cannot be used in global ORDER clause

okay, i will help you fix this problem, if you would kindly test and then comment on each of the following two queries

first query –

SELECT swd.id
     , swd.ix_id
     , swd.title
     , swd.descpt
     , swdcategory
  FROM swd
INNER
  JOIN swv
    ON swv.meta_id = swd.id
   AND swv.keyword = '$find'
   AND swv.vote_up > 0
 WHERE swd.display = 'yes'

second query –

SELECT id
     , ix_id
     , title
     , descpt
     , category
  FROM swd
 WHERE MATCH(title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10)
       AGAINST('$find')
   AND display = 'yes' 

do these two queries separately produce the correct results? please also explain why the second query appears to have paging ($start,$page) but the first one doesn’t

No. The 1st SELECT is way off the Mark. It will not work at all since the vote_up is coming from the 2nd Table of swv.
I see that my question is too difficult to answer via this forum. And by the time I explain the complexities of the problem that will become a problem on to its own.
So I have to work out the problem myself.
Thank anyway.

a bit of an exaggeration, i think

and what, may i ask, is wrong with this –

swv.vote_up > 0

seems like that’s exactly what you said it should be

duh…