Choosing the 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. Therefore, for each product there should be 1 result with price and supplier
If you've tried one hundred queries 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)
FK product to product Supplier 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 it is.
Thank you in advance
source to share
I understand that your question means that you want the supplier to have the lowest price for each product.
CTE orders vendors at a price per product, and the main request only uses this order to get the vendor with the lowest price.
NOTE . As I use RANK
, if multiple vendors have the same lowest price, they will all be refunded. If this is not correct, change RANK
toROW_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
source to share