3

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)

Sam Hosseini
  • 813
  • 2
  • 9
  • 17
Yarin
  • 173,523
  • 149
  • 402
  • 512

3 Answers3

2

You have some extra brackets.

See this SQL Fiddle

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
1
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;
Nesim Razon
  • 9,684
  • 3
  • 36
  • 48
1

Remove the outermost parentheses. The SELECT statement should start with the SELECT keyword, not an open paren.

spencer7593
  • 106,611
  • 15
  • 112
  • 140