Select unique rows with latest timestamp without changing original order-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Just sort it as required by the DISTINCT ON, then re-sort it as required by the desired output order. You will need to add a subquery to add another ORDER BY.

select * from (
  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, a.last_active desc nulls last
) foo order by registration_date desc nulls last;