How to solve the problem of simultaneous use of UNION and Order by in Mysql joint Query

Therefore, such errors often occur.
Copy codeThe Code is as follows:
Select * from [IND] where INDID> 10
Union
Select * from [IND] where INDID <9
So far, no questions have been asked

Later, someone may use similar queries.
Copy codeThe Code is as follows:
Select * from [IND] where INDID> 10 order by INDID desc
Union
Select * from [IND] where INDID <9 order by INDID desc
The database reports an error. The problem lies in order.

Why? Can UNION and order by not exist at the same time?

Of course, union and order by can coexist.

However, when union is used, union queries are not only used to merge data sets.
Instead of connecting each subquery to one query, the whole data set is obtained after the entire query statement is understood in the database.
In addition, order by can only appear once in a data set query and appears at the end.
Therefore, in the Union query, order by must be written after the last subquery, and the order is to sort the result set of the Union query, not just sorting the last subquery
Copy codeThe Code is as follows: select * from [IND] where INDID> 10
Union
Select * from [IND] where INDID <9 order by INDID desc
In this way, we can sort the result set in the Combined Query, instead of the result set in the last subquery.

Perform another experiment to fully illustrate this problem.

Create such a query
Copy codeThe Code is as follows:
Select * from [IND] where INDID = 4

Union

Select * from [IND] where INDID = 2
Union
Select * from [IND] where INDID = 1
Union
Select * from [IND] where INDID = 5
Union
Select * from [IND] where INDID = 3
INDID is the primary key. When creating data, the order in the database is 12345.

If the Union query only joins the query results, the result is 42153.

However, the actual result is 12345 in the same order as the data in the database.

Therefore, we can conclude that the results of the Combined Query are obtained after the entire query is completed, rather than splicing the subquery one by one.
Copy codeThe Code is as follows:
Select * from [IND] where INDID = 4

Union

Select * from [IND] where INDID = 2
Union
Select * from [IND] where INDID = 1
Union
Select * from [IND] where INDID = 5
Union
Select * from [IND] where INDID = 3

Order by indid asc/DESC
In this way, the entire Union result set can be sorted.

What's more about TOP?

If so, in normal queries, TOP is executed after order by, then
Copy codeThe Code is as follows:
Select TOP 2 * from [IND] where INDID = 4

Union

Select * from [IND] where INDID = 2
Union
Select * from [IND] where INDID = 1
Union
Select * from [IND] where INDID = 5
Union
Select * from [IND] where INDID = 3

Order by INDID
In this way, can we get the first two pieces of data after sorting the entire result set?

The answer is no.

Although in a single sentence query, TOP is executed after order by, but in a joint query, the scope of TOP is in the subquery, therefore, TOP does not filter the result set of the joint query, but only filters the subquery that it writes in. This is like the WHERE statement in the subquery, the scope of filtering such as this is in subqueries, unlike the order by function in the entire Union query.

How can we filter the Top N pieces of data in a joint query? Simple

Use rowcount

Compared with TOP, rowcount is more standard as the result set truncation. After all, it does not rely on query statements, but directly sets the number of result sets obtained by query statements.
Copy codeThe Code is as follows:
Set rowcount 2

Select * from [IND] where INDID = 4

Union
Select * from [IND] where INDID = 1
Union
Select * from [IND] where INDID = 2

Union
Select * from [IND] where INDID = 3

Order by INDID ASC
See the preceding query statement. We can sort the Union query and obtain the top two pieces of data.

Since order by can be used to sort and rowcount can be used to intercept the set and quantity, other applications such as natural Union query paging can also be used.