MySQL UNION SELECT statement order by two different columns

Question

I have a table with a column called "myorder". I want to only order the first 5 elements in ascending order by using the column "myorder" and then the rest of the elements ordererd by id in descending order. I've tried but I don't seem to get this 100%. I have created a sqlfiddle with this simple example. Can anybody help me?

The order in the sqlfiddle should be: Blue, Brown, Yellow, Red, Green, Orange, Gray, Black, White.

[This](http://www.daniweb.com/web-development/databases/mysql/threads/312461/union-two-queries-and-preserve-sort) website suggests that an `order by` statement can only be used after the final `select` on a union. Is it possible to reverse the order of the `MYORDER` column where `1` and `5`, and `2` and `4` are swapped? — Adam, Apr 01 '14 at 21:21
Can we have NULL value instead of zero when myorder is not specified? — AllInOne, Apr 01 '14 at 21:34

score 0 · Answer 1 · answered Apr 01 '14 at 21:19

Append this to the end of your sql statement:

ORDER BY FIELD(id, 3,4,5,1,2,9,8,7,6)

This will manually sort the results based on the id field in the specified order getting the colours in order.

Alternatively you could:

ORDER BY FIELD(NAME, BLUE,BROWN,YELLOW......)

Dani · Answer 2 · 2014-04-01T21:28:39.203

Union does not preserves order, at least not in all databases. To get this done you need to select a fake column and use order on the results (total)

Here is a solution:

(

SELECT Id, myorder, myorder as fake, name from tbl WHERE myorder!=0) UNION 
(SELECT id, myorder, 2000 as fake,name  from tbl WHERE myorder=0) ORDER BY fake asc, id desc

This Will work in any case, as long as you keep the fake number high enough.

score 0 · Accepted Answer · answered Apr 01 '14 at 21:24

0

maybe try

SELECT * from tbl ORDER BY IF(myorder=0, 2147483647, myorder) ASC, id DESC

if by "first 5" you mean ones with nonzero myorder

answered Apr 01 '14 at 21:24

guest

6,450
30
44

Hi, thanks for your answer. What does that number in the middle do? A little explanation please. I'm the kind of person who wants to understand and not copy paste. Thank you! – Mike Apr 01 '14 at 21:48
1

In your order, you have it so that the nonzero `myorder` come first. This query accomplishes this by replacing zero values with a large number. How large? As large as an `int` can be in MySQL (http://dev.mysql.com/doc/refman/5.7/en/integer-types.html). – guest Apr 01 '14 at 21:50

score 0 · Answer 4 · edited May 23 '17 at 10:31

0

Can we have NULL value instead of zero when myorder is not specified?

If so we can do this:

SELECT * from tbl ORDER BY -myorder DESC, id DESC

Fiddle:

http://sqlfiddle.com/#!2/ef9ee/4

Hint on forcing nulls to sort last: MySQL Orderby a number, Nulls last

edited May 23 '17 at 10:31

Community

1
1

answered Apr 01 '14 at 21:28

AllInOne

1,450
2
14
32

score 0 · Answer 5 · answered Apr 01 '14 at 21:47

0

Use a CASE statement and the following trick (for example):

SELECT *, 
CASE myorder
    WHEN 0 THEN id
    ELSE 99999999 - myorder
END AS ord
FROM tbl
ORDER BY ord DESC

SQL Fiddle

answered Apr 01 '14 at 21:47

Rafa Paez

4,820
18
35

MySQL UNION SELECT statement order by two different columns

5 Answers5