I have a table called history with the fields id, lastname, event and date.
It saves events of persons that works in my office like "Entering office", "Exiting office", "Illness", "Holidays", etc.
I need to get the last event of every person.
This is what I tried but it doesn't work:
SELECT lastname, event, max(date)
FROM personshistory
GROUP BY lastname, event;
You can use distinct on:
select distinct on (lastname) ph.*
from personshistory
order by lastname, date desc;
distinct on is a very convenient Postgres extension. It keeps one row for each value of the expressions in parentheses. The specific row is determined by the order by clause -- based on the keys that follow the distinct on keys.
With NOT EXISTS:
SELECT p.lastname, p.event, p.date
FROM personshistory p
WHERE NOT EXISTS (
SELECT 1 FROM personshistory
WHERE lastname = p.lastname and date > p.date
)
or join your query (without the event column) to the table:
SELECT p.lastname, p.event, p.date
FROM personshistory p INNER JOIN (
SELECT lastname, MAX(date) maxdate
FROM personshistory
GROUP BY lastname
) g on g.lastname = p.lastname and g.maxdate = p.date
