0

I have a list of different products with matching characteristics and different prices. What I need to do is find the lowest price for each unique item. Each item has a product_id, sku_number, size, and price. I need to return the lowest price for items that share the same sku_number and size. The unique identifier is the product_id.

Here is an example of the table:

Product_ID Sku Size Price
143194557 DC077 9.5 $138
142488366 DC077 9.5 $137
143201917 DC077 9.5 $140

I need the query to return only product_id 142488366.

The query I thought would work was, as an example:

select p.id, p.sku_number, p.size, min(p.price) from products as p 
where
p.sku = '1111111' and p.size = '9.5' or 
p.sku = '2222222' and p.size = '10'
group by 1,2,3

and so on and so on. I have to do this with about 400 different products. Unfortunately this query returns every product that matches the criteria, not just the one with the minimum price, which is what I am looking for, so I get like 30 results for each sku/size combination. I'm sure this is a simple query, but my brain is fried and I'm having trouble wrapping my head around it.

Any help is appreciated. Thanks!

EZ-Ryder
  • 11
  • 1
  • What makes a product unique? sku/size or just sku? You can use a windowed MIN function. – squillman Aug 09 '22 at 18:10
  • Sorry, every item has a unique product_id. – EZ-Ryder Aug 09 '22 at 18:17
  • Every _row_ has a unique product_id, but does that carry through to the product? Are there multiple rows with the same sku (but different product_id)? Or does a different size create a different sku? Sample data would help a lot here. – squillman Aug 09 '22 at 18:18
  • What happens if you add this: "HAVING p.price = MIN(p.price)" ? – JohnH Aug 09 '22 at 18:29
  • I added in a sample data – EZ-Ryder Aug 09 '22 at 18:36
  • But not sufficient sample data. You could use TOP 1 order by PRICE to achieve your desired result with this sample but that is obviously much too simplistic. This seems like a standard "first in group" issue - a term you can use for searching. And FYI - use parentheses to control how multiple boolean conditions are evaluated (esp. mixing AND and OR). – SMor Aug 09 '22 at 19:23

0 Answers0