# Thread: Why ORDER BY is being lost in case of UNION

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

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

Code:
```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```

3. Originally Posted by WorldNews
What is going on?
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

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

Originally Posted by r937
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

5. Originally Posted by WorldNews
So what do you suggest?
depends on what you are trying to achieve and why

did you try the solution in post #2?

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

7. Originally Posted by WorldNews
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:
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.

Code:
```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
Code:
```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```

8. Originally Posted by WorldNews
I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
doesn't this say something about the wisdom of posting fake code

and no, that wasn't a question

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

Originally Posted by DaveMaxwell
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.

Code:
```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
Code:
```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```

10. 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 --
Code:
```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 --
Code:
```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

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

12. Originally Posted by WorldNews
No. The 1st SELECT is way off the Mark.
a bit of an exaggeration, i think

Originally Posted by WorldNews
It will not work at all since the vote_up is coming from the 2nd Table of swv.
and what, may i ask, is wrong with this --
Code:
`swv.vote_up > 0`
seems like that's exactly what you said it should be

Originally Posted by WorldNews
I see that my question is too difficult to answer via this forum.
duh...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•