Ardeth Ardeth - 4 months ago 13
SQL Question

Listing the minimum price products for current month

I have a table called "fiyatlar" in a MySql database. Table contains multiple product id's and prices per month and year. Please check the example table below:

ID Price Month Year
11111 11.00 5 2016
11111 12.15 6 2016
11111 13.10 7 2016
22222 8.75 5 2016
22222 8.50 6 2016
22222 8.00 7 2016


I need to find the minimum priced products for current month and list them in a datagrid.

Thank you in advance.

Answer

This should do it

SELECT ID
FROM fiyatlar
WHERE Price = 
    (SELECT MIN(Price)
     FROM fiyatlar
     WHERE Year = YEAR(current_date())
     AND Month = MONTH(current_date()))

UPDATE based on newly understood requirements:

I couldn't stand not solving this. :) This will work:

SELECT fiyatlar.ID
FROM fiyatlar INNER JOIN
    (SELECT ID, MIN(Price) AS Price
    FROM fiyatlar GROUP BY ID) AS tmp
ON fiyatlar.ID = tmp.ID
    AND fiyatlar.Price = tmp.Price
WHERE Year = YEAR(current_date())
    AND Month = MONTH(current_date());

This was my test table for repeating minimum values:

11111   12.15   6   2016
11111   11.00   5   2016
11111   10.90   10  2015
11111   10.90   7   2016
22222   9.00    9   2015
22222   8.75    5   2016
22222   8.70    7   2015
22222   8.50    6   2016
22222   7.90    4   2015
22222   7.70    8   2015
22222   7.70    7   2016
Comments