Return greater value of a SQL-specific record

Navigation
3

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

I've tried to use MAX() , but it returns the highest value of all of the table, as well as a ORDER BY to sort from highest to lowest and a LIMIT to get only the largest.

The summary search 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 to only return the value with the higher date. This way, it returns two lines as a result, it would solve to use a LIMIT or ORDER BY in the end, however, in the complete SQL it has more JOINS with other subquerys, anyway.

The bank briefly looks like this:

animals: 

ID  |  NOME 
20  |  Teste

animal_movement: 

ID | ID_ANIMAL | DATA
1  | 20        | 01/07/2016
2  | 20        | 10/07/2016
    
asked by anonymous 12.07.2016 / 16:30

1 answer

0

You can use group by to group the data and 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
    
12.07.2016 / 16:41
Autocomplete with image and text How to get a column in the database, perform a calculation and send to grid