CODEWITHSUNDEEP
sqlsql-servert-sqlselectgroup-by
Kevin Kamer
Kevin Kamer

Reputation: 99

Selecting lowest price for multiple products

Hy all,

For a project i need an overview of all products to find the supplier with the lowest price. So for every product there must be 1 outcome with a price and supplier

If have tried like a hundred query but i just can't find the right one.

Tables:

CREATE TABLE [dbo].[product](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [picture_name] [varchar](255) NOT NULL)


CREATE TABLE [dbo].[supplier](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [varchar](50) NOT NULL)

CREATE TABLE [dbo].[supplier_overview_product](
 [supplier] [int] NOT NULL,
 [product] [int] NOT NULL,
 [price] [real] NOT NULL)

Product is FK to product Supplier is FK to supplier

This is what i have:

SELECT s.name, MIN(sop.price)
FROM dbo.supplier_overview_product AS sop
JOIN dbo.product AS p
ON sop.product = p.id
JOIN dbo.supplier AS s
ON s.id  = sop.supplier
GROUP BY s.name

But there is no supplier. And i want to know who that is.

Thanks in advance

Upvotes: 4

Views: 2414

Answers (1)

Bort
Bort

Reputation: 7638

I'm understanding your question to mean you want the supplier with the lowest price for each product.

The CTE orders the suppliers by price for each product, and the main query uses that ordering to only retrieve the supplier with the lowest price.

NOTE: Because I'm using RANK, if multiple suppliers happen to have the same lowest price, they will all be returned. If this is incorrect, change RANK to ROW_NUMBER

;WITH SuppliersByPrice AS (
    SELECT product, supplier, price,
        RANK() OVER (PARTITION BY product ORDER BY price) as ord
    FROM supplier_overview_product
)
SELECT SBP.product, SBP.price, S.name
FROM SuppliersByPrice SBP
    INNER JOIN Supplier S ON S.id = SBP.supplier
WHERE SBP.ord = 1

Upvotes: 4

Related Questions