Say I have the following query:
SELECT id, name, registration_date
FROM users
ORDER BY registration_date DESC nulls last;
This results in:
id name registration_date
-----------------------------------------------
1a John Doe 2020-08-21 17:00:41+0000
2a Joe Darwin 2020-07-21 20:14:10+0000
3a Jim Lee 2020-07-15 16:14:10+0000
I have another table activity where there are multiple records for each user when they were logging in:
For example
id user_id last_active
----------------------------------------
1. 1a 2021-05-18 16:14:01+0000
2. 1a 2021-05-17 10:14:01+0000
3. 2a 2021-05-21 12:14:01+0000
4. 2a 2021-05-19 12:14:01+0000
5. 3a 2021-04-17 00:00:01+0000
6. 3a 2021-03-01 00:00:01+0000
I need to do a join on this table to select the most recent last_active timestamp for each user. I can do it with the following query:
SELECT DISTINCT ON(u.id) u.id, u.name, u.registration_date, a.last_active
FROM users
LEFT JOIN activity a ON u.id = a.user_id
ORDER BY u.id, registration_date DESC nulls last;
However, this changes the original order of the original result. I don't want to order by u.id -- I need the query to be ordered by registration_date and simply add another column for each row with the most recent last_active date. How can I achieve this?
Moin,
I had same problem times ago, i saved it by using row number over. So in the first step you will create a unique list with the latest login and second you can join it.
SELECT * FROM(
SELECT
ROW_NUMBER() OVER (PARTITION BY user_id order by last_active desc) oNR,
User_ID,
last_Active
FROM activity
)s
WHERE s.oNR = 1
Now you can join this to your user
SELECT distinct on(u.id) u.id, u.name, u.registration_date, a.last_active
FROM users
left join
(
SELECT * FROM(
SELECT
ROW_NUMBER() OVER (PARTITION BY user_id order by last_active desc) oNR,
User_ID,
last_Active
FROM activity
)s
WHERE s.oNR = 1
)
a
on u.id = a.user_id
order by u.id, registration_date desc nulls last;
sorry for the bad formatting.
Best Regards.