I have a table with different dates and different prices and I want to get the minimum value of those dates:
SELECT t1.nombre, min(t1.precio), t1.fecha_desde, t1.fecha_hasta
FROM tabla1 t1
WHERE t1.nombre = 'un_nombre'
AND t1.precio != "-"
AND t1.fecha_hasta BETWEEN CURDATE() AND '2017-12-31'
GROUP BY t1.nombre, t1.precio, t1.fecha_desde, t1.fecha_hasta
The result is as follows:
nombre | min(t1.precio) | fecha_desde | fecha_hasta
------------------------------------------------------------------
un_nombre 117 2017-03-30 2017-05-28
un_nombre 132 2017-03-30 2017-05-28
un_nombre 91 2017-05-31 2017-07-31
un_nombre 107 2017-05-31 2017-07-31
un_nombre 95 2017-08-01 2017-09-30
un_nombre 111 2017-08-01 2017-09-30
But I would need only the minimum value:
nombre | min(t1.precio) | fecha_desde | fecha_hasta
------------------------------------------------------------------
un_nombre 117 2017-03-30 2017-05-28
un_nombre 91 2017-05-31 2017-07-31
un_nombre 95 2017-08-01 2017-09-30
In your original query
You are grouping by price. If price is in the GROUP BY clause, I understand that query will return the different prices for each range (NxM), but not the minimum for each range (1xM).
The query should be
EDIT:
Looking at the fiddle, I see that it
precio
is a string, and therefore it is sorted alphabetically. A string starting with 1 is less than one starting with 9, so 117 is less than 91.You have to convert it to a number in the form: