Mysql queries UNION and Order by jointly using the error reporting solution

2020-06-15 10:23:07
OfStack

As a result, such mistakes often occur


select * from [IND] where INDID>10
union
select * from [IND] where INDID<9

So far, no questions have been asked

Later, someone might use a similar query


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 reports an error. That's the problem with order by

Why is that? Can't UNION and ORDER BY exist together?

union and order by can certainly exist together

But with union, federated queries are more than just merging data sets
He does not join each subquery one by one at the beginning of 1. The database understands the whole query statement and then combines the query to get the whole data set
In addition, order by can only appear once and appear at the end of a data set query.
Therefore, in a federated query, order by is written after the last subquery, and the sort is ordered on the result set of the entire federated query, not just on the last subquery

select * from [IND] where INDID>10
union
select * from [IND] where INDID<9 order by INDID desc

This allows us to sort the result set as a whole, rather than just the result set of the last subquery.

Do another experiment to illustrate the problem more fully

Create 1 such query


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, and at the time of data creation, the order in the database is 12345

If the federated query merely joins the query results at 1, we should get a result of 42153

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

Therefore, it can be concluded that the results of the joint query are obtained after the entire query is completed, rather than being pieced together after sub-queries are completed one by one.


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 joint result set to be sorted.

Also about TOP?

If so, TOP is executed after ORDER BY in a normal query


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

Does that give you the first two pieces of sorted data for the entire result set?

The answer is no.

Although said in query sentences, TOP is in ORDER BY after execution, but in a federated query, write, TOP scope is in the subquery, therefore TOP didn't set selection, the results of joint query and only for it is written in the note to filter in the query, it is like in the subquery WHERE statements 1 sample, like this the selection scope of is in the subquery, unlike ORDER BY role in the whole joint query.

Then how to filter the top N data for the joint query? Very simple

Use rowcount

rowcount is more canonical as a result set truncation header than TOP, after all, does not rely on the query statement, but sets the number of result sets that the query gets.


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

Look like the above query statement. We can sort the federated query and get the top two pieces of data.

Now that you can sort with order by and use rowcount intercepts and quantities, other applications such as natural federated query paging are no problem