Filter placement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Filter placement

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-02-04 : 19:17:05

Placing "where CustomerType is not null" inside the subquery gives me a higher result (count) than placing it outside, but I'm not sure why. Ideas?



select s.ID
from (
select row_number() over (partition by a.ID order by a.DateUpdated desc) as [DateRank], a.ID, a.DateUpdated, a.Type, a.CustomerType
from db1.dbo.table1 a
where a.CustomerType is not null
) s
where s.[DateRank] = 1

vs.



select s.ID
from (
select row_number() over (partition by a.ID order by a.DateUpdated desc) as [DateRank], a.ID, a.DateUpdated, a.Type, a.CustomerType
from db1.dbo.table1 a
) s
where s.[DateRank] = 1
and s.CustomerType is not null

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-04 : 19:42:14

Because the FROM statement deterimes the result set. a WHERE clause filters that result set.

This:


from db1.dbo.table1 a
where a.CustomerType is not null

Produces less results for use in the ROW_NUMBER() PARTITION clause, and therefore will have MORE ROW_NUMBER() = 1.

Poor planning on your part does not constitute an emergency on my part.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-02-04 : 20:09:48

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources