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
