MySQL: column grouping and date range

Question

I have a table like this

id - price - date 1 - 10 - 01/01/2017 2 - 10 - 02/01/2017 3 - 10 - 03/01/2017 4 - 10 - 04/01/2017 5 - 10 - 05/01/2017 6 - 20 - 06/01/2017 7 - 20 - 07/01/2017 8 - 20 - 08/01/2017 9 - 20 - 09/01/2017 10 - 10 - 10/01/2017 11 - 10 - 11/01/2017 12 - 10 - 12/01/2017 13 - 10 - 13/01/2017 

And I want to show the result this way

 10 - 01/01/2017 - 05/01/2017 20 - 06/01/2017 - 09/01/2017 10 - 10/01/2017 - 13/01/2017 

My request

 SELECT price, min(date), max(date) FROM mytable GROUP BY price 

but the result

 20 - 06/01/2017 - 09/01/2017 10 - 10/01/2017 - 13/01/2017 

Is it possible to get the correct result using mysql query or find php solution?

Answer 1

This is a problem of gaps and islands. You can use variables to detect islands of consecutive records with the same price value:

 SELECT price, MIN(`date`) AS start_date, MAX(`date`) AS end_date FROM ( SELECT id, price, `date`, @rn := @rn + 1 AS rn, -- If price remains the same then increase island population, -- otherwise reset it @seq := IF(@p = price, @seq + 1, IF(@p := price, 1, 1)) AS seq FROM mytable CROSS JOIN (SELECT @p := 0, @rn := 0, @seq := 0) AS vars ORDER BY `date`) AS t GROUP BY price, rn - seq 

Answer 2

 SELECT price, min(date), max(date) FROM mytable GROUP BY price order by price