Search code examples
mysqlsqlsql-order-bylimitunion-all

Mysql: Use order by and limit into separate queries when using union all

I know that MySql syntax does not allow us to do what i'm seeking, but i'm asking for a workaround for my problem.

I want to run multiple queries and use union to display the full set of data by using order by and limit per each query. I've used fake parameters and tables names due to legal issues.

select 
    x,
    y,
    z
from tbl1
where z = 'xxxx'
and y = 111
and x = 'text'
order by rand()
limit 11966
union all
select 
    x,
    y,
    z
from tbl1
where z = 'xxxx'
and y = 222
and x = 'text'
order by rand()
limit 3560
union all
select
.
.
.
.
.

Does anyone knows a workaround to this?

Solution

  • Use parentheses:

    (select ... order by ... limit ...) 
union all
(select ... order by ... limit ...) 
union all
(select ... order by ... limit ...) 
union all
...