Return higher value of a specific SQL record

Asked

Viewed 5,678 times

4

I need my SQL to return the longest date, but not the longest date among all records, and yes, the longest date of a specific id.

I’ve tried using the MAX(), but it returns the highest value of all of the table as well as a ORDER BY to sort from the largest to the smallest and a LIMIT to catch only the largest.

The short research is as follows:

    SELECT cob.id, animais.nome, cob.data
    FROM animais
    LEFT JOIN (SELECT id, id_animal, data FROM animais_movimento WHERE 
    id_tipo_movimento = 1) AS cob ON animais.id = cob.id_animal

From it, I would like you to return only the value with the higher date. This way, returns two lines as a result, I would decide to use a LIMIT or ORDER BY at the end, however, in the complete SQL has more JOINS with other subquerys, anyway.

The bank briefly goes like this:

animals:

ID  |  NOME 
20  |  Teste

animals_movement:

ID | ID_ANIMAL | DATA
1  | 20        | 01/07/2016
2  | 20        | 10/07/2016
sql date

  • and you want me to return that last line?

  • Yes, in this example, I want you to return only the record with ID 2, which is the one with the longest date.

  • See if the legal answer answers

  • Thanks, it helped a lot, but not completely, with it I was able to build SQL, but it’s not 100% yet, I will post more details as an answer.

  • 1

    I managed to solve the problem, is returning right I needed to only give an adapted to implement in full SQL, thanks for the help.

1 answer

0


You can use the group by to group the same data and the max to get the last date.

SELECT max (cob.id), animais.nome, max(cob.data)
FROM animais
 JOIN animais_movimento cob 
ON animais.id = cob.id_animal
group by animais.nome

Browser other questions tagged sql date

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