The Union clause in MYSQL does not support the solution for order by _mysql

This example describes a workaround for the Union clause in MySQL that does not support order by. Share to everyone for your reference, specific as follows:

I know very little about db, this problem is only encountered in MySQL, I do not know other DBMS is not the case.

The problem is, I'm going to get two rows next to a record in one table, and I want to take it out with the union, so it says:

Select Id,title from subjects where id> #some_id # ORDER by ID limit 1
Union
Select Id,title from subjects where id< #some_id # ORDER by ID limit 1

However, there was an error indicating "incorrect usage of UNIONand order by". It seems impossible to use Union and order by, but it does need to be here. Soon, I came up with a flexible formulation:

SELECT * FROM (
select Id,title to subjects where id> #some_id # ORDER by ID limit 1
) union
Select Id,titl E from subjects where id< #some_id # ORDER by ID limit 1

From experience, the second clause should not be affected by the Union, which can be used by the order. So the first clause is wrapped in parentheses, which should be OK. However, there are still errors, prompting "Every derived table must have its own alias." The hint here is that we need to alias the temporary table generated in parentheses, which is much better. Then read:

SELECT * FROM (
select Id,title to subjects where id> #some_id # ORDER by ID limit 1
) as T1 union
Select I D,title from subjects where id< #some_id # ORDER by ID limit 1

The statement was executed successfully and got the correct result, written in this memo.

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.