I have a working query that I need to repeat a few times, however I'm getting syntax errors on the UNION ALL:
Working query:
set @num := 0, @group := '';
select person, `group`, age
from
(
select person, `group`, age,
@num := if(@group = `group`, @num + 1, 1) as row_number,
@group := `group` as dummy
from mytable
order by `Group`, Age desc
) as x where x.row_number <= 2;
Failing UNION ALL (Gets a syntax error):
set @num := 0, @group := '';
(
select person, `group`, age
from
(
select person, `group`, age,
@num := if(@group = `group`, @num + 1, 1) as row_number,
@group := `group` as dummy
from mytable
order by `Group`, Age desc
) as x where x.row_number <= 2;
)
UNION ALL
(
select person, `group`, age
from
(
select person, `group`, age,
@num := if(@group = `group`, @num + 1, 1) as row_number,
@group := `group` as dummy
from mytable
order by `Group`, Age desc
) as x where x.row_number <= 2;
)
(The UNIONed queries are just duplicates of the original query for testing purposes)
You have some extra brackets.
See this SQL Fiddle