Search code examples
mysqlsqlunion-all

Can't UNION ALL two equivalent queries?


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;

SQL FIDDLE

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;
)

SQL FIDDLE

(The UNIONed queries are just duplicates of the original query for testing purposes)


Solution

  • You have some extra brackets.

    See this SQL Fiddle