Tuesday, March 09, 2010

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

1 comment:

des said...

Thanks, just encountered this exact same problem and this fixed it perfectly.

Most Recent Post

Community Updates

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape