I am not so into database and I am finding some problem trying to modify a query defined on MySql.
I have this query that is my starting poing (the query that have to be modified):
SELECT
MC_PS.id AS price_series_id,
MC_PS.market_commodity_details_id AS market_commodity_details_id,
MC_PS.price_date AS price_date,
MC_PS.avg_price AS avg_price,
CU.ISO_4217_cod AS currency,
MU.unit_name AS measure_unit,
MD_CD.market_details_id AS market_id,
MD_CD.commodity_details_id AS commodity_id
FROM Market_Commodity_Price_Series AS MC_PS
INNER JOIN MeasureUnit AS MU
ON MC_PS.measure_unit_id = MU.id
INNER JOIN Currency AS CU
ON MC_PS.currency_id = CU.id
INNER JOIN MarketDetails_CommodityDetails AS MD_CD
ON MC_PS.market_commodity_details_id = MD_CD.id
This query returns the prices of some commodities in some market, this is the query output:
price_series_id market_commodity_details_id price_date avg_price currency measure_unit market_id commodity_id
-----------------------------------------------------------------------------------------------------------------------------------------------------
17 25 18/12/2017 778 RWF kilogram 4 6
70 32 17/12/2017 5 RWF kilogram 6 16
71 32 18/12/2017 6 RWF kilogram 6 16
3 26 15/12/2017 12,84 XOF kilogram 4 8
4 25 15/12/2017 18,233 XOF kilogram 4 6
5 25 16/12/2017 22,84 XOF kilogram 4 6
6 24 16/12/2017 18,94 XOF kilogram 4 4
7 24 17/12/2017 28,63 XOF kilogram 4 4
9 26 18/12/2017 48,37 XOF kilogram 4 8
11 24 18/12/2017 33,33 XOF kilogram 4 4
18 28 18/12/2017 13,7 XOF kilogram 5 4
19 28 17/12/2017 16,2 XOF kilogram 5 4
20 28 16/12/2017 14,9 XOF kilogram 5 4
21 30 18/12/2017 21,47 XOF kilogram 5 3
22 30 16/12/2017 22,39 XOF kilogram 5 3
23 30 17/12/2017 23,17 XOF kilogram 5 3
24 26 17/12/2017 16,22 XOF kilogram 4 8
26 26 16/12/2017 19,39 XOF kilogram 4 8
27 31 18/12/2017 13,2 XOF kilogram 5 8
28 31 17/12/2017 14,4 XOF kilogram 5 8
29 31 16/12/2017 12,6 XOF kilogram 5 8
31 25 17/12/2017 17,8 XOF kilogram 4 6
32 25 17/12/2017 14,8 XOF kilogram 4 6
33 29 18/12/2017 8,2 XOF kilogram 5 1
34 29 17/12/2017 9,6 XOF kilogram 5 1
35 29 16/12/2017 9,3 XOF kilogram 5 1
As you can see it contains the price of a commodity (commodity_id) sold into a market (market_id) in a specific day (price_date).
I need to modify my query in such a way that show only the last price of a commodity sold in a market.
Practically if in the previous result there are multiple record related to a specific commodity sold in a specific market related different dates, have to be returned a single record for the last date.
So the output of my modified query have to be something like this one:
price_series_id market_commodity_details_id price_date avg_price currency measure_unit market_id commodity_id
-----------------------------------------------------------------------------------------------------------------------------------------------------
71 32 18/12/2017 6 RWF kilogram 6 16
9 26 18/12/2017 48,37 XOF kilogram 4 8
11 24 18/12/2017 33,33 XOF kilogram 4 4
18 28 18/12/2017 13,7 XOF kilogram 5 4
21 30 18/12/2017 21,47 XOF kilogram 5 3
27 31 18/12/2017 13,2 XOF kilogram 5 8
17 25 18/12/2017 778 RWF kilogram 4 6
33 29 18/12/2017 8,2 XOF kilogram 5 1
As you can see for each market_id and commodity_id fields are retrieved only the records related to the last price_date value
How can I correctly implement this change?
Maybe can I use a GROUP BY used with an HAVING clause in some way?
Does your Market_Commodity_Price_Series table have an autoincrementing id column? Does your application always INSERT new rows with recent price_date values into it?
If so, you can add a WHERE clause to your query as follows
WHERE MC_PS.id IN ( SELECT MAX(id) id
FROM Market_Commodity_Price_Series
GROUP BY market_commodity_details_id )
This will filter out all but the highest id value for each item. If the assumptions are true, that will also be the latest value.
IF those assumptions do not hold true, you can use a similar kind of filter but it's harder. To get the latest id value for each item you do this:
SELECT a.id
FROM Market_Commodity_Price_Series a
JOIN (
SELECT MAX(price_date) price_date,
market_commodity_details_id
FROM Market_Commodity_Price_Series
GROUP BY market_commodity_details_id
) b ON a.market_commodity_details_id = b.market_commodity_details_id
AND a.price_date = b.price_date
This query looks at your table and finds the latest date for each item. It then recovers the id value of that item. You can then use that whole subquery in a WHERE id IN () clause like the one shown above.