DENSE_RANK with subquery

Question

I have products that come in different requests so if the same products come in 2 requests I want to return the last request id.

I have used DENSE_RANK to take unique products based on request ID but it gives me different ranks when I am using it as a subquery and I noticed that I am facing the issue if I have products that are related only to one RequestId as the following data:

Product   StatusId    ProductID   RequestId
    62    60                75    10
    62    60                79    10
    62    60                23    10
    62    60                 7    10

the query:

    SELECT [Product], [StatusId], [ProductId],
                   DENSE_RANK() OVER (PARTITION BY [Product] ORDER BY [RequestId] DESC) ranked_order 
FROM [Size] where ranked_order = 1

the result I got:

Product   StatusId    ProductID   ranked_order 
    62    60                75    1
    62    60                79    1
    62    60                23    1
    62    60                 7    1

here as subquery:

SELECT  [Product], [StatusId], [ProductId],ranked_order
       FROM (
SELECT [Product], [StatusId], [ProductId],
                   DENSE_RANK() OVER (PARTITION BY [Product] ORDER BY [RequestId] DESC) ranked_order 
          FROM [Size]) pz  where ranked_order = 1

and it returns the data but there are some missing products.

so I have added the following condition where ProductId in (1,2,3) to trace the error for some missing products and I noticed the rank changes from 1 to 2.

and as I mentioned before it appears if the products are related only to one RequestId.

Product   StatusId    ProductID   ranked_order 
    62    60                75    2
    62    60                79    2
    62    60                23    2
    62    60                 7    2

Answer 1

In your first query applies the condition where ProductId in (1,2,3) before the dense_rank is calculated, because they appear in the same query.

In the second query, you calculate the dense_rank first, in the sub-query, then apply where ProductId in (1,2,3) in the outer query. Presumably your table contains data that would be ranked first before that condition is applied.