The solution to the problem that the Union clause in MySQL does not support order by_MySQL

The example in this article describes the solution to the problem that the Union clause in MySQL does not support order by. Share it with everyone for your reference, the details are as follows:

I don’t know much about DB. This problem is only encountered in MySQL. I don’t know if it is the same in other DBMS.

The problem is this. I plan to get two rows adjacent to a certain row in a table, and I want to take them out together through union, so I write this:

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

But the error message "Incorrect usage of UNION and ORDER BY" appeared. It seems that union and order by cannot be used in this way, but order by is indeed required here. Soon, I thought of an alternative way of writing:

select * from (
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

From experience, the second clause should not be affected by union, so you can use order by. So wrap the first clause in parentheses and it should be fine. But there is still an error, prompting "Every derived table must have its own alias". The tip here is that we need to give an alias to the temporary table generated in the brackets, which is much easier to handle. So it was changed to:

select * from (
select id,title from subjects where id>#some_id# order by id limit 1
) as t1 union
select id,title from subjects where id<#some_id# order by id limit 1

This statement was successfully executed and the correct result was obtained. Write a memo here.

Readers who are interested in more MySQL-related content can check out the special topics on this site: "A Complete Collection of MySQL Log Operation Skills", "A Summary of MySQL Transaction Operation Skills", "A Complete Collection of MySQL Stored Procedure Skills", and "A Summary of MySQL Database Lock Related Skills" 》and《Summary of commonly used functions in MySQL》

I hope this article will be helpful to everyone in MySQL database planning.