How to select the last record of a table in Postgree SQL by User-CodePudding

I have the following DB in PostgreSQL:

I want to make a query where I select from the table "activity-history-tracking" the last "tracking" of a user. That is to say, I want to consult in the DB the last record of the user and what was his tracking

I want to achieve something like this:

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
...

From where we get the user, taking into account that in "activity-history-follow-up" the history (follow-up) of the users is stored daily. How can I do this query in Postgree SQL?

I tried something like the following but it 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

CodePudding user response：

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