SQL Server 2008 R2 pagination

I need to implement pagination for my union query, but I get error "Msg 102, Level 15, State 1, Lin开发者_开发百科e 14 Incorrect syntax near ')'.". I followed example I found from this link.

select * 
  from (select Id, 
               row_number() OVER (order by Id asc) as RowNumber 
          from (select Id 
                  from (select Id 
                          from Table1) as table1 
                union all 
                select Id 
                  from (select Id 
                          from Table2) as table2)) as t Derived 
  WHERE RowNumber > 5 
    and RowNumber <= 10

Use:

SELECT u.* 
  FROM (SELECT t.id, 
               ROW_NUMBER() OVER (ORDER BY t.id) as rownum 
          FROM (SELECT t1.id 
                  FROM TABLE1 t1
                UNION ALL 
                SELECT t2.id 
                  FROM TABLE2 t2) as t) AS u
 WHERE u.rownum > 5 
   AND u.rownum <= 10

It looks to me that your query was missing a closing bracket for a derived table called "derived", but there wasn't the need for the subqueries in the UNION so I've removed them.

You just need to move one parenthesis:

from Table2) as table2)) as t Derived should read from Table2) as table2) as t) Derived

You could also remove some of the subqueries that make Table1 into table1 and Table2 in to table2 but I assume there's some other reaon for those in there (like this is based on another query that is more complicated)

select * 
  from (select Id, 
               row_number() OVER (order by Id asc) as RowNumber 
          from (select Id 
                  from Table1 as table1 
                union all 
                select Id 
                  from Table2)p)t 
  WHERE RowNumber > 5 
    and RowNumber <= 10

If you select from subquery then you have to give alias for it. You have 2 outer subqueries but only one with alias. Should be: from Table2) as tabl2) as t) as t