MYSQL order by field and union, sorting as desired

MYSQL

order by field and union, sorting as desired

First, there is a table called testbl which I mainly use.

mysql> select * from testbl;
+ ------- + ------ + ------ + -------- +
| sid | ssid | sort | status |
+ ------- + ------ + ------ + -------- +
| 1 | aa | 23 | e |
| 2 | aa | 11 | e |
| 3 | aa | 33 | e |
| 4 | aa | 32 | e |
| 5 | bb | 23 | a |
| 6 | bb | 67 | c |
| 7 | bb | 34 | a |
| 8 | bb | 77 | d |
| 9 | cc | 11 | a |
| 10 | cc | 22 | a |
| 11 | cc | 32 | d |
| 12 | cc | 23 | c |
+ ------- + ------ + ------ + -------- +
12 rows in set (0.00 sec)

I'm going to try a few things with this table.
Once you have sorted out the results of our usual order by, you can do the following to show them in random order rather than in a certain order.

mysql> select * from testbl where sid in (3,4,1) by order field (sid, 3, 4, 1);
+ ------- + ------ + ------ + -------- +
| sid | ssid | sort | status |
+ ------- + ------ + ------ + -------- +
| 3 | aa | 33 | e |
| 4 | aa | 32 | e |
| 1 | aa | 23 | e |
+ ------- + ------ + ------ + -------- +
3 rows in set (0.00 sec)

You can specify the order you want in the same way as order by field (column name, "order 1", "order 2", ....).

However, when you want to do this in the order you want, it is usually ... most of the time you have to show what you've already seen and show up only the results you want (I did.)
Then you can write union. 

mysql> (select * from testbl where sid in (3,4,1) order by field (sid, 3, 4, 1))
union 
(select * from testbl where sid <9);
+ ------- + ------ + ------ + -------- +
| sid | ssid | sort | status |
+ ------- + ------ + ------ + -------- +
| 1 | aa | 23 | e |
| 3 | aa | 33 | e |
| 4 | aa | 32 | e |
| 2 | aa | 11 | e |
| 5 | bb | 23 | a |
| 6 | bb | 67 | c |
| 7 | bb | 34 | a |
| 8 | bb | 77 | d |
+ ------- + ------ + ------ + -------- +
8 rows in set (0.00 sec)

I looked at the query above and pulled a little more into the union. But ... the alignment is a mess. I did not order it by order by. The reason is that "UNION does not suggest anything about the order in which the records should appear in the final result, because the recordset is created out of sequence." If you combine it with a union in a word, it means that the order is arbitrary .
So when you do this, you have to sort out the objects you have drawn.

mysql> (select * from testbl where sid in (3,4,1))
union 
(select * from testbl where sid <9) 
order by field (sid, 3, 4, 1);
+ ------- + ------ + ------ + -------- +
| sid | ssid | sort | status |
+ ------- + ------ + ------ + -------- +
| 5 | bb | 23 | a |
| 2 | aa | 11 | e |
| 8 | bb | 77 | d |
| 7 | bb | 34 | a |
| 6 | bb | 67 | c |
| 3 | aa | 33 | e |
| 4 | aa | 32 | e |
| 1 | aa | 23 | e |
+ ------- + ------ + ------ + -------- +
8 rows in set (0.00 sec)

If you do the above, you will see 3, 4, and 1 aligned. However, it is still insufficient. The rest of the sequences were messy, and I wanted 3, 4, 1 to be picked from the top.
In this case, you can do the following.

mysql> (select * from testbl where sid in (3,4,1))
union 
(select * from testbl where sid <9) 
order by field (sid, 1, 4, 3) desc, sid;
+ ------- + ------ + ------ + -------- +
| sid | ssid | sort | status |
+ ------- + ------ + ------ + -------- +
| 3 | aa | 33 | e |
| 4 | aa | 32 | e |
| 1 | aa | 23 | e |
| 2 | aa | 11 | e |
| 5 | bb | 23 | a |
| 6 | bb | 67 | c |
| 7 | bb | 34 | a |
| 8 | bb | 77 | d |
+ ------- + ------ + ------ + -------- +
8 rows in set (0.00 sec)

It is important to note that in order to extract desc, we need to set the order of 3, 4, and 1 in the order of 1, 4, and 3 so that we can get the desired result.

Well, it sounds like a good idea when it comes to handling things like announcements.

// ----------------------------- Additions 2012.10.24
In MySQL 5.1.41, which I have experimented with, it does not matter if you execute the query as above, but depending on the environment, an error may occur depending on whether you put a single parenthesis or blank after the field.
If you get an error, try not to make a space between the field and the parentheses.