Mysql Union Query Union and order by using simultaneous error resolution _MYSQL

Therefore, there are often such errors

Copy Code code as follows:

SELECT * FROM [IND] where indid>10
Union
SELECT * FROM [IND] where indid<9

So far, there has been no question of

After that, maybe someone will use a similar query.

Copy Code code as follows:

SELECT * FROM [IND] where indid>10 order BY indid Desc
Union
SELECT * FROM [IND] where indid<9 order BY indid Desc

At this point there is a problem, the database error. The problem is on the order by

Why, then? Does union and order by cannot exist at the same time?

Union and order by of course can exist simultaneously

But when using union, union queries are not just merging data collections
He is not a query for each subquery out after a join together, the database is the whole query to understand the sentence after the unified query to get the entire data collection
In addition the order by in a data set query can only appear once and appear at the end.
Therefore, in a union query, the order by is written after the last subquery, and the sort is sorted on the result set of the entire Union query, not just the last subquery

Copy Code code as follows:

SELECT * FROM [IND] where indid>10
Union
SELECT * FROM [IND] where indid<9 order BY indid Desc

Instead of sorting the result set of the last subquery, we can sort the result set out of our federated query.

Do another experiment to explain the problem more fully.

To create a query like this

Copy Code code 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, the order in the database when creating the data is 12345

If the federated query is only saved and joins the query results together, then the result should be: 42153

However, the result is actually the same as the order of the data in the database 12345

Therefore, it can be concluded that the result of the joint query is the completion of the entire query, rather than the subquery after the completion of splicing.

Copy Code code 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

This allows the entire federated result set to be queued.

Another about top?

If so, in a normal query, top is executed after the order by, then

Copy Code code 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

So is it possible to get the first two data after the entire result set is sorted?

The answer is no.

Although it is said in the query of the simple sentence, top is executed after the order by, but in a union query, this writes that the top scope is in the subquery, so top does not filter the result set of the federated query, but only the query in which it is written. This is like a where statement in a subquery, and such a filter is scoped to a subquery, unlike an order-by effect on the entire federated query.

So how to the joint query to intercept the top n data filter it? Very simple

With ROWCOUNT

Compared to top, rowcount as a result set to intercept more standardized, after all, not rely on query statements, but directly set the query statement to get the number of result sets.

Copy Code code 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

In the form of the above query statement. We can sort the union query and get the top two data.

Since it is possible to sort by and use rowcount to intercept sets and quantities, other applications, such as natural union query paging, can be a cinch.