HELP: Row_Number and WHERE range issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

HELP: Row_Number and WHERE range issue

Author

Topic

minimaximus
Starting Member

3 Posts

Posted - 2010-04-10 : 05:58:47

Hi,

I'm having a strange issue with Row_Number. I'm trying to specify a WHERE that involves 5 tables (for search), and so I need all the rows in all the tables, even if some records don't have corresponding rows in some of the tables, or if some tables are empty. I'm getting all the rows user left joins, which works fine, but generates duplicates...

Now Distinct removes the duplicates just fine. However, I'm also returning the row_number for paging.

so my overall query looks like this:

select * from
(
select distinct t1.*, ROW_NUMBER() OVER
(order by t1.ID ASC) AS RowNumber
from table1 t1
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t3.id = t2.id
left join table4 t4 on t4.id = t2.id
left join table5 t5 on t5.id = t2.id
where <search criteria>
) as T

where RowNumber between 1 and 20 ------- Problem Here
order by RowNumber

Now this works fine if I remove the RowNumber 'between' statement, but if I add it in, I get duplicate rows in the outside select. I can't use DISTINCT on the outside select because the rows are considered unique due to the row number... Any ideas?

Thanks,

Al

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-10 : 06:31:53

your problem is the row_number(). It will generate a running number starting from 1 for each row which also means the row_number() is a unique

so your distinct t1.*, row_number() . . will basically not return you distinct record for table t1

if you wanted only the 20 records, remove the row_number() and just use top 20 in your query

KH
[spoiler]Time is always against us[/spoiler]

minimaximus
Starting Member

3 Posts

Posted - 2010-04-10 : 06:41:15

Thanks for your reply. The internal select does return a set of distinct rows, but when you add the outside WHERE statement it all breaks....

Al

quote:
Originally posted by khtan

your problem is the row_number(). It will generate a running number starting from 1 for each row which also means the row_number() is a unique

so your distinct t1.*, row_number() . . will basically not return you distinct record for table t1

if you wanted only the 20 records, remove the row_number() and just use top 20 in your query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-10 : 06:49:45

it is not the where condition. It is the row_number with distinct.

if you need the row_number of pagination ..

put the row_number() outside of the distinct query


select *
from
(
    select *, row_number() over (order by . . .) as ..
    from
    (
        select distinct . ..
        from   ...
    ) b
) a
where RowNumber between 1 and 20

KH
[spoiler]Time is always against us[/spoiler]

minimaximus
Starting Member

3 Posts

Posted - 2010-04-10 : 08:56:01

quote:
Originally posted by khtan

it is not the where condition. It is the row_number with distinct.

if you need the row_number of pagination ..

put the row_number() outside of the distinct query

select *
from
(
select *, row_number() over (order by . . .) as ..
from
(
select distinct . ..
from ...
) b
) a
where RowNumber between 1 and 20

KH
[spoiler]Time is always against us[/spoiler]

That worked perfectly! Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources