SELECT Only Cheapest Or Most Expensive Variant From Many

I have a table of product variants, grouped by a product key. I also have a category table which is joined off the ProductKey. So if I query for products in a category or many categories I will get a list variants that match the joined ProductKeys.

SELECT Pk, ProductKey, Name, Description, Price
FROM ProductVariant
INNER JOIN Product2Category ON ProductVariant.ProductKey = Product2Category.ProductKey 
INNER JOIN Category ON Product2Category.CategoryKey = Category.pk WHERE Category.pk IN(@categorykeys)

Which would result in example data like this

Pk, ProductKey, Name, Description, Price
20, 1, 'Product One - Variant One', 'Description', 25.65
21, 1, 'Product One - Variant Two', 'Description', 24.65
22, 1, 'Product One - Variant Three', 'Description', 29.65
23, 2, 'Product Two - Variant', 'Description', 26.65
24, 2, 'Product Two - Variant', 'Description', 23.65
25, 2, 'Product Two - Variant', 'Description', 25.65

The issue is I just want to return a single product per ProductKey. Either the cheapest of the group. So the results would be

Pk, ProductKey, Name, Description, Price
21, 1, 'Product One - Variant Two', 'Description', 24.65
24, 2, 'Product Two - Variant', 'Description', 23.65

Or the most expensive. So the results would be

Pk, ProductKey, Name, Description, Price
22, 1, 'Product One - Variant Three', 'Description', 29.65
23, 2, 'Product Two - Variant', 'Description', 26.65

How can I do this efficiently within the SELECT sql query? Currently I’m returning all and doing the filtering in memory on the server in C#.

Answer

If I understand you correctly, you need to use ROW_NUMBER() to get the expected results:

Table:

CREATE TABLE Data (
   Pk int, 
   ProductKey int, 
   Name varchar(100), 
   Description varchar(50), 
   Price numeric(10, 2)
)
INSERT INTO Data 
   (Pk, ProductKey, Name, Description, Price)
VALUES   
   (20, 1, 'Product One - Variant One', 'Description', 25.65),
   (21, 1, 'Product One - Variant Two', 'Description', 24.65),
   (22, 1, 'Product One - Variant Three', 'Description', 29.65),
   (23, 2, 'Product Two - Variant', 'Description', 26.65),
   (24, 2, 'Product Two - Variant', 'Description', 23.65),
   (25, 2, 'Product Two - Variant', 'Description', 25.65)

Statement for the cheapest product:

SELECT 
   t.Pk, t.ProductKey, t.Name, t.Description, t.Price
FROM (
   SELECT 
     Pk, ProductKey, Name, Description, Price,
     ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY Price ASC) AS Rn
   FROM Data
) t
WHERE t.Rn = 1

Statement for the most expensive product:

SELECT 
   t.Pk, t.ProductKey, t.Name, t.Description, t.Price
FROM (
   SELECT 
     Pk, ProductKey, Name, Description, Price,
     ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY Price DESC) AS Rn
   FROM Data
) t
WHERE t.Rn = 1

Results:

-------------------------------------------------------------
Pk  ProductKey  Name                        Description Price
-------------------------------------------------------------
21  1           Product One - Variant Two   Description 24.65
24  2           Product Two - Variant       Description 23.65

-------------------------------------------------------------
Pk  ProductKey  Name                        Description Price
-------------------------------------------------------------
22  1           Product One - Variant Three Description 29.65
23  2           Product Two - Variant       Description 26.65