I've tried this, but the correct value is not coming
SELECT id, data, max(valor)
FROM teste
WHERE data BETWEEN '2017-03-01 00:00:00' AND '2017-04-01 00:00:00'
The maximum value comes perfect, but it takes the first id within that date. How to get the id relative to the max value? If possible, date too.
I think this works, but maybe there is a better solution:
SELECT id, data, max(valor)
FROM teste
WHERE
data BETWEEN '2017-03-01 00:00:00' AND '2017-04-01 00:00:00'
AND
valor in (select max(valor) FROM teste)
SELECT id,data,valor FROM teste WHERE
data BETWEEN '2017-03-01 00:00:00' AND '2017-04-01 00:00:00'
ORDER BY valor DESC
LIMIT 1;
SELECT TOP 1 id, data, valor
FROM teste
WHERE data BETWEEN '2017-03-01 00:00:00' AND '2017-04-01 00:00:00' order by valor desc
You can use a subquery or use an INNER JOIN.
One option is to use INNER JOIN :
SELECT id,
data,
valor
FROM teste
INNER JOIN (SELECT MAX(valor) AS valor
FROM teste
WHERE data BETWEEN '2017-03-01 00:00:00' AND
'2017-04-01 00:00:00') AS j
USING (valor)
The INNER JOIN will catch everything that is between the defined dates and will return max() . Then, using Valor as the base (in USING() ) we will get all data that has the same value set by MAX() .
Another option is simply to make a valor = (SELECT ...) , for example:
SELECT id,
data,
valor
FROM teste
WHERE valor = (SELECT MAX(valor)
FROM teste
WHERE data BETWEEN '2017-03-01 00:00:00' AND
'2017-04-01 00:00:00')
This form is easier to understand and performs the same as in the other method, first it takes the maximum value (by MAX() ) and then it takes all information where valor is equal to MAX() .