CODEWITHSUNDEEP
sqlsql-server
YodasMyDad
YodasMyDad

Reputation: 9485

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#.

Upvotes: 2

Views: 196

Answers (3)

Gospodin Tanev
Gospodin Tanev

Reputation: 66

Perhaps you can also try using the cross apply operator like :

SELECT c2.Pk, v.ProductVariant,  c2.Price
FROM ProductVariant as v CROSS APPLY
     (SELECT TOP(1) Price, Pk
      from Product2Category as c2 
      where v.ProductVariant = c.ProductVariant order by price desc
     ) as o inner join
     Category as c
     on c2.CategoryKey = c.CategoryKey  -- this will return the highest price for each product, inverse the order by inside the cross apply and will therefore return the lowest.

I am not familiar with the structure of your tables but the logic is there as the cross apply method as inline is quite fast.

Upvotes: 3

Zhorov
Zhorov

Reputation: 29993

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

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

You can try this below logic. Remember, this is not the exact query, just sample query where from you can take necessary logical help.

SELECT * FROM your_table A
INNER JOIN 
(
    SELECT ProductKey,MIN(Price) min_price,MAX(Price) max_price
    FROM your_table
    GROUP BY ProductKey
)B ON A.ProductKey = B.ProductKey
AND(
    A.Price = B.Min_Price
    OR 
    A.Price = B.max_price
)

Upvotes: 2

Related Questions