Get the id of where max(value) was found

Asked

Viewed 196 times

3

I tried it like this, but it’s not the right amount

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 picks up the first id within that date. How do I get the id of the max value? If possible the date also.

mysql

4 answers

1


I think it works that way, but maybe there’s 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)

1

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;
  • 3

    Could explain the query?

  • Sort by the highest value in descending order, and take only the first result.

0

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

  • I think this would only apply to SQL Server, I personally don’t know this for Mysql. Out of curiosity I tried to run this on Mariadb and results in "You have an error in your SQL syntax", would be a hand-in-a-wheel. :(

-1

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 take everything that is between the dates set and will return the max(). Then, using the Valor as a basis (in USING()) we will take all the data that have the same value defined by MAX().

Another option is to simply make one 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 than in the other method, first takes the maximum value (by MAX()) and then get all the information where the valor is equal to MAX().

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.