The Union clause in MySQL does not support the order by solution. The union clause
This example describes how the Union clause in MySQL does not support order. We will share this with you for your reference. The details are as follows:
I know very little about DB. This problem only happens in MySQL, and I don't know if other DBMS is the same.
The problem is that I want to obtain two adjacent rows with a row of records in a table and want to obtain them through union, so I write:
select id,title from subjects where id>#some_id# order by id limit 1unionselect id,title from subjects where id<#some_id# order by id limit 1
However, the error message "Incorrect usage of UNION and order by" appears ". It seems that union and order by cannot be used, but order by is indeed required here. Soon I came up with a work und:
select * from (select id,title from subjects where id>#some_id# order by id limit 1) unionselect id,title from subjects where id<#some_id# order by id limit 1
In terms of experience, the second clause will not be affected by union. order by can be used. So we can wrap the first clause in a bracket. However, an error still occurred, prompting "Every derived table must have its own alias ". The prompt here is that we need to get an alias for the temporary table generated in the brackets, which is much easier. So it is changed:
select * from (select id,title from subjects where id>#some_id# order by id limit 1) as t1 unionselect id,title from subjects where id<#some_id# order by id limit 1
If this statement is successfully executed and the correct result is obtained, write it down and forget it.