Solution on RowNum and Union Query Problem

I was trying to create stored procedure for Paging data.

Query which I tried using to fetch data contains union. I want RowNum for this union query and I was trying to do create query as follow.

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) DataSet1

Above query works fine till I perform where clause on RowNum field. As RowNum is not part of subquery, when I tried to perform filter on RowNum field I was getting following error.

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) MyTable

Where

RowNum BETWEEN (1 * 25) + 1 AND (1 * 25) + 25

Error: Invalid column name 'RowNum'

Solution:

In order to resolve this situation I have placed my Query like this.

Select *

From

(

My Query

) MyTable

Where Clause for RowNum filter

In order to resolve Row_Number() function problem with Union Query, I have modified my query to following.

Select *

From

(

Select Id,

Title,

ROW_NUMBER() OVER(ORDER BY Title Desc) as RowNum

from

(

select Col1 as Id,Col2 as Title from Table1

union all

select Col1 as Id, Col2 as Title from Table2

union all

select Col1 as Id,Col2 as Title from Table3

) MyTable1

) MyTable2

Where

RowNum BETWEEN (1 * 25) + 1 AND (1 * 25) + 25