CODEWITHSUNDEEP
mysql
Matt Millen
Matt Millen

Reputation: 23

MySql - Selecting MAX & MIN and returning the corresponding rows

I trying to get the last 6 months of the min and max of prices in my table and display them as a group by months. My query is not returning the corresponding rows values, such as the date time for when the max price was or min..

I want to select the min & max prices and the date time they both occurred and the rest of the data for that row... (the reason why i have concat for report_term, as i need to print this with the dataset when displaying results. e.g. February 2018 -> ...., January 2018 -> ...)

SELECT metal_price_id, CONCAT(MONTHNAME(metal_price_datetime), ' ', YEAR(metal_price_datetime)) AS report_term, max(metal_price) as highest_gold_price, metal_price_datetime FROM metal_prices_v2 
WHERE metal_id = 1
AND DATEDIFF(NOW(), metal_price_datetime) BETWEEN 0 AND 180
GROUP BY report_term
ORDER BY  metal_price_datetime DESC

I have made an example, extract from my DB: http://sqlfiddle.com/#!9/617bcb2/4/0

My desired result would be to see the min and max prices grouped by month, date of min, date of max.. and all in the last 6 months.

thanks

UPDATE. The below code works, but it returns back rows from beyond the 180 days specified. I have just checked, and it is because it joining by the price which may be duplicated a number of times during the years.... see: http://sqlfiddle.com/#!9/5f501b/1

Upvotes: 1

Views: 1916

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

simplified version of what you should do so you can learn the working process.

You need calculate the min() max() of the periods you need. That is your first brick on this building.

you have tableA, you calculate min() lets call it R1

SELECT group_field, min() as min_value
FROM TableA
GROUP BY group_field

same for max() call it R2

SELECT group_field, max() as max_value
FROM TableA
GROUP BY group_field

Now you need to bring all the data from original fields so you join each result with your original table

We call those T1 and T2:

SELECT tableA.group_field, tableA.value, tableA.date
FROM tableA
JOIN ( ... .. ) as R1
  ON tableA.group_field = R1.group_field
 AND tableA.value = R1.min_value


SELECT tableA.group_field, tableA.value, tableA.date
FROM tableA
JOIN ( ... .. ) as R2
  ON tableA.group_field = R2.group_field
 AND tableA.value = R2.max_value

Now we join T1 and T2.

SELECT *
FROM ( .... ) as T1
JOIN ( .... ) as T2
  ON t1.group_field = t2.group_field

So the idea is if you can do a brick, you do the next one. Then you also can add filters like last 6 months or something else you need.

In this case the group_field is the CONCAT() value

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could use twice inner join on the subselect for min and max 

select   a.metal_price_datetime
         , t1.highest_gold_price
         , t1.report_term
         , t2.lowest_gold_price
         ,t2.metal_price_datetime

  from metal_prices_v2 a
  inner join (
      SELECT  CONCAT(MONTHNAME(metal_price_datetime), ' ', YEAR(metal_price_datetime)) AS report_term
               , max(metal_price) as highest_gold_price

      from metal_prices_v2
      WHERE metal_id = 1
      AND DATEDIFF(NOW(), metal_price_datetime) BETWEEN 0 AND 180
      GROUP BY report_term
  ) t1 on t1.highest_gold_price = a.metal_price
  inner join (
     select   a.metal_price_datetime
         , t.lowest_gold_price
         , t.report_term

  from metal_prices_v2 a
  inner join (
      SELECT  CONCAT(MONTHNAME(metal_price_datetime), ' ', YEAR(metal_price_datetime)) AS report_term
               , min(metal_price) as lowest_gold_price

      from metal_prices_v2
      WHERE metal_id = 1
      AND DATEDIFF(NOW(), metal_price_datetime) BETWEEN 0 AND 180
      GROUP BY report_term
  ) t on t.lowest_gold_price = a.metal_price

 ) t2 on t2.report_term = t1.report_term

Upvotes: 3

Related Questions