UNION and ORDER BY issue in MySQL

Question

You should see what I'm trying to do here but it's not working

$getquery = "SELECT * 
FROM highscore 
WHERE score >= '$score'
ORDER BY score ASC
LIMIT 6

UNION

SELECT * 
FROM highscore 
WHERE score < '$score'
ORDER BY score DESC
LIMIT 5";

mysql_error() returns: "improper usage of ORDER BY and UNION".

A union query can normally only have an order-by clause in the LAST query being unioned, which is applied to the results of the union. Using brackets (as in Erwin's answer below) lets you order the individual queries and bypass this limitation. — Marc B, Oct 03 '11 at 17:35

score 9 · Accepted Answer · edited May 23 '17 at 11:55

9

Try:

$getquery = "(SELECT * 
FROM highscore 
WHERE score >= '$score'
ORDER BY score ASC
LIMIT 6)

UNION ALL -- guaranteed to be beneficial in this case as Johan commented

(SELECT * 
FROM highscore 
WHERE score < '$score'
ORDER BY score DESC
LIMIT 5)";

See the comments to my answer on the related question.
Or consult the fine manual.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 03 '11 at 17:34

Erwin Brandstetter

605,456
145
1,078
1,228

1

Also @CanSpice, doesn't each subselect require its own alias? And a `UNION ALL` will be substantially faster, esp since there can be no overlap here. – Johan Oct 03 '11 at 17:40
@Johan; These are not actual subselects, so they don't. I added a link to mysql docs. But good point on `UNION ALL`! – Erwin Brandstetter Oct 03 '11 at 17:50

score 4 · Answer 2 · answered Oct 03 '11 at 17:35

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT * 
 FROM highscore 
 WHERE score >= '$score'
 ORDER BY score ASC
 LIMIT 6)

UNION

(SELECT * 
 FROM highscore 
 WHERE score < '$score'
 ORDER BY score DESC
 LIMIT 5)

UNION and ORDER BY issue in MySQL

2 Answers2

Linked