CODEWITHSUNDEEP
sqlsql-serverwhere-clausewindow-functionsrow-number
Mahdi Yusefi
Mahdi Yusefi

Reputation: 521

using row_num() and using filtering value from window function in WHERE CLAUSE query

I have stored procedure.SELECT statement is like below

select  
*, row_number() OVER (ORDER BY ProductRN.ID) as RNN
from  (
SELECT 

p.ID
,p.ProductCode
,p.ProductNameF
,p.ProductNameE
,p.[Weight]
,p.PackageTypeID
,p.ISForeign
,p.BrandID
,b.BrandNameF
,b.BrandNameE
,p.ImgID
,p.CategoryID
,p.AmountInBox
,pc.PropertyGroupID
,p.Comment
,p.CompanyID
,p.Status
,po.CompanyNameF
,PriceConsumer=MKT.Fn_GetProductPrice(1,p.ID, @CurDate)
,PriceRetail=MKT.Fn_GetProductPrice(2,p.ID, @CurDate)
,PriceWholesale=MKT.Fn_GetProductPrice(3,p.ID, @CurDate)
,pg.PropertyName as PropertyGroupName
,b.LogoID
from mkt.Product as p (nolock)
inner join mkt.ProductCategory as pc (nolock) on pc.ID = p.CategoryID
inner join mkt.Brand b (nolock) on p.BrandID=b.ID
inner join MKT.ProductOwner as po (nolock) on po.ID = b.BrandOwnerID
left  join MKT.PropertyGroup as pg (nolock) on pc.PropertyGroupID=pg.ID

) AS  ProductRN 

where ProductRN.CompanyID=@CompanyID 
and  ProductRN.Status=1 
and  ProductRN.CategoryID=@ProductCategoryID
AND  (ProductRN.BrandID=@BrandID OR @BrandID=0)
--SOME WAY TO FILTER RNN

end

there is not any way to use RNN Column in WHERE CLAUSE because we can't use Column aliases and window function inside WHERE CLAUSE. actually the problem is that when i put 

row_number() OVER (ORDER BY ProductRN.ID) as RNN

inside inner SELECT statement, RNN column exactly become equal to p.ID (Product.ID) it seems sql server first calculates row_number() and then perform the WHERE CLAUSE.

How can I change this query so that row_num() apply to final query records and I can filter it in WHERE CLAUSE.

Upvotes: 0

Views: 687

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Use a subquery or CTE:

select t.*
from (<your query goes here>) t
where rnn = 1;

Upvotes: 4

Related Questions