ROW_NUMBER() with OUTER APPLY

Question

I have a query that returns the lowest Price of a ProductVariation by using ROW_NUMBER() to enumerate the rows in groupings (partitions) of the ProductId, sorted by the Price ascending (MIN function returning multiple rows after adding column to SELECT).

SELECT 
   pv.Id AS VariationId, 
   pv.ProductId, 
   pv.[Description], 
   pv.Price, 
   pv.Stock,
   ROW_NUMBER() OVER (PARTITION BY pv.ProductId ORDER BY pv.Price, pv.Stock DESC) AS SortId
FROM ProductVariation pv
INNER JOIN Product p ON p.Id = pv.ProductId
WHERE p.CompanyId = @CompanyId

One Product can have multiple variations. Below I have my ProductVariation table.

This query works perfeclty and returns the following results:

VariationId	ProductId	Description	Price	Stock	SortId
149	97	Yellow Fruits	139.99	0.00	1
150	97	Red Fruits	139.99	5.00	2
151	97	Green Apple	139.99	20.00	3
152	98	Peach Mango	219.99	0.00	1
153	99	Crazy Lemon	169.99	1.00	1
154	99	Arctic Ice	169.99	3.00	2
155	99	Paradise Punch	169.99	3.00	3

However, now we have another table called ProductValueConsultor in which the consultor can update the ProductVariation price individually.

Inside ProductValueConsultor we have a row that updates the ProductVariation price to $149.00 for the id 149.

StoreId	Price	ProductVariationId
13	149.90	149

So now, I updated my query and added the OUTER APPLY operator to identify possible values on ProductValueConsultor table and show the updated prices.

SELECT 
   pv.Id AS VariationId, 
   pv.ProductId, 
   pv.[Description], 
   ISNULL(pValue.Price, pv.Price) Price, 
   pv.Stock,
   ROW_NUMBER() OVER (PARTITION BY pv.ProductId ORDER BY pv.Price) AS SortId
FROM ProductVariation pv
OUTER APPLY (
   SELECT TOP 1 * FROM ProductValueConsultor pvc
   WHERE pv.Id = pvc.ProductVariationId AND pvc.StoreId = @StoreId
) pValue
INNER JOIN Product p ON p.Id = pv.ProductId
WHERE p.CompanyId = @CompanyId

The OUTER APPLY operator worked perfeclty, showing the updated price for the ProductVariation id 149. However, the ROW_NUMBER() function broked, showing the sorted order for the original prices.

VariationId	ProductId	Description	Price	Stock	SortId
149	97	Yellow Fruits	149.99	0.00	1
150	97	Red Fruits	139.99	5.00	2
151	97	Green Apple	139.99	20.00	3
152	98	Peach Mango	219.99	0.00	1
153	99	Crazy Lemon	169.99	1.00	1
154	99	Arctic Ice	169.99	3.00	2
155	99	Paradise Punch	169.99	3.00	3

How can I fix this query to apply the ROW_NUMBER() function on the updated values by the OUTER APPLY?

score 1 · Accepted Answer · answered Jun 06 '23 at 16:19

Just change the ORDER BY in the row-number

SELECT 
   pv.Id AS VariationId, 
   pv.ProductId, 
   pv.[Description], 
   ISNULL(pValue.Price, pv.Price) Price, 
   pv.Stock,
   ROW_NUMBER() OVER (
          PARTITION BY pv.ProductId
          ORDER BY ISNULL(pValue.Price, pv.Price)) AS SortId
FROM ProductVariation pv
OUTER APPLY (
    SELECT TOP (1) pvc.*
    FROM ProductValueConsultor pvc
    WHERE pv.Id = pvc.ProductVariationId
      AND pvc.StoreId = @StoreId
) pValue
INNER JOIN Product p ON p.Id = pv.ProductId
WHERE p.CompanyId = @CompanyId;

The TOP (1) is suspicious as it has no ORDER BY. Assuming ProductValueConsultor is actually a many-many join table, you should always have exactly one row anyway so it's not necessary to add TOP (1). Otherwise add an ORDER BY to the APPLY

ROW_NUMBER() with OUTER APPLY

1 Answers1