Showing Distinct Values With Aggregates

I have a table for recording daily price from different suppliers. My goal is to find the best (low price) supplier. The table structure is Table Name: lab1 Columns: ID, Product_I

Solution 1:

You can use self join with the product id and minimum amount of price to get the lowest price row per product id

select l.ID,
l.Product_ID,
monthname(l.Price_Date) `Month`,
l.Price,
l.Supplier
from lab1 l
join (select Product_ID,min(Price) Price
     from lab1
     groupby Product_ID) l1
using(Product_ID,Price)

DEMO

Solution 2:

select temp2.id,
       temp2.Product_ID,
       DATENAME(month, temp2.Price_Date) AS MONTH,
       temp1.Min_Price,
       temp2.Supplier
from
(
     select Product_ID, min(Price) as Min_Price
     from lab1
     groupby Product_ID
) as temp1
inner join
lab1 temp2
on temp1.Product_ID = temp1.Product_ID
and temp1.Min_Price = temp2.Min_Price

Solution 3:

I think you are looking for:

select l.ID, l.Product_ID, monthname(l.Price_Date) as Month, l.Price, l.Supplier

from lab1 l join
     (select Product_ID, year(l.Price_date) as yr, month(l.Price_Date) as mon, min(Price) as Price
      from lab1
      groupby Product_ID, year(l.Price_date), month(l.Price_Date)
     ) lmin
     on l.Product_id = lmin.Product_id andyear(l.Price_Date) = lmin.yr andmonth(l.Price_Date) = lmin.mon;

If you want data only for October, then add a where clause:

where l.Price_Date >= '2014-10-01' and l.Price_Date < '2014-11-01'