Return the last entry of different Postgresql objects

Question

Supposing I have two tables: Car containing: plate, year, ect.. Event containing: car, lat, long, date, time, etc.. How could I return only the last entry of each car in the event table?

Answer 1

It would be something like:

SELECT C.*, E.*
FROM CARRO C
JOIN EVENTO E
    JOIN E.CARRO_ID = C.ID
    AND E.DATA_HORA = (
            SELECT MAX(DATA_DATA) 
            FROM EVENTO E2
            WHERE E2.CARRO_ID = E.CARRO_ID
        )

The explanation:

1. The Join selects the car and all its events through the expression E.CARRO_ID = C.ID
2. The subselect only the most recent date of all events for that car
3. Thus, the second clause of the merge causes the query to only return the event whose date is the highest

Note that the field should have the date and time and should not have two events at the same time, otherwise you can select two events. There is a way around this, but it would be almost a gambiarra, it is more reasonable not to have two events at the same time.

Answer 2

distinct on return only one row for each carro_id. In the clause order by decide which one.

select distinct on (carro_id) *
from
    carro
    inner join
    evento using(carro_id)
order by carro_id, data desc, hora desc

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

Answer 3

If you’re just looking for the data, you can use the following query...

SELECT DISTINCT evt.carro, evt.lat, evt.long, evt.data, evt.hora, etc...
FROM Evento evt, Carro car
WHERE evt.carro = car.placa AND (evt.carro, evt.data) IN
                                            (SELECT carro, data
                                             FROM Evento, Carro
                                             WHERE data >= ALL AND Evento.carro = Carro.placa)

The DISTINCT is used to avoid repetitions.