How to select the last record of a table by user in PostgreSQL

Question

I have the following database in PostgreSQL:

How do I select from the table activity-history-tracking the last tracking of a user?

id username      date tracking
------------------------------
1  Aaron     2/1/2010 1600
2  Freddy    3/1/2010 2000
3  Jimin     8/4/2009 3000
4  Brad      2/2/2010 4000
5  Gus      12/2/2009 1000
...

In activity-history-follow-up the history (follow-up) of the users is stored daily.

The following didn't work:

SELECT *
FROM(Select
    user_id,
    Max(date) as lastActivity
    from "activity-historic-tracked"
    Group By user_id) as result
GROUP BY user_id, lastActivity

Answer 1

Starting from your existing aggregate query that brings the latest activity date per user, we can just bring the user table with a join so we can access the user name:

select
    t.id_user,
    max(t.date) as lastactivity,
    u.username
from "activity-historic-tracked" t
inner join "user" u on u.id = t.id_user
group by t.id_user, u.id

If, on the other hand, you need to lookup the entire latest history record for each user (eg if there are more columns that you are interested in other than the latest date), then we can filter rather than aggregate ; distinct on comes handy for this:

select distinct on (t.id_user) t.*, u.username
from "activity-historic-tracked" t
inner join "user" u on u.id = t.id_user
order by t.id_user, t.date desc