Problem with union clause in MySQL 5.7 and its solution

As per the below mentioned change in MySQL 5.7 official documentation.

Change Type	Whats Changed
Incompatible change	In UNION statements, to apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT. Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.

Here is an example to make you understand this.

If using 'union' with 'order by' clause without parentheses it will return error.

Example:

select EId,ENTId from(

select te.EId,te.ENTId from t_ent te order by te.ENTId

union

select teli.EId,teli.ENTId from t_ent_line_item teli order by teli.ENTId

)test ;

Result:

Error Code: 1221. Incorrect usage of UNION and ORDER BY

There are two solutions.

1. use parentheses separately

Example:

select EId,ENTId from(

(select te.EId,te.ENTId from t_ent te order by te.ENTId)

union

(select teli.EId,teli.ENTId from t_ent_line_item teli order by teli.ENTId)

)test;

2. use 'order by' clause in outer query

Example:

select EId,ENTId from(

select te.EId,te.ENTId from t_ent te

union

select teli.EId,teli.ENTId from t_ent_line_item teli

)test order by ENTId;

Recommended to use solution 1(use parentheses separately.)