Select the row with the most recent modified date

I have this table:

user_id	name	email	modified_date
1	John	[e-mail]	2022-01-01
1	John	[e-mail]	2022-01-02
1	Lucy	[e-mail]	2022-01-03
2	Joey	[e-mail]	2021-12-24
3	Mike	[e-mail]	2022-01-01
3	Mary	[e-mail]	2022-01-02

I’m trying to get unique user_id‘s email with the most recent modified_date. This is my expected output:

user_id	email	name
1	[e-mail]	Lucy
2	[e-mail]	Joey
3	[e-mail]	Mary

I used limit 1, but the output seems to be randomized.

Can somebody help me to sort this out ?

Answer

you can go for row_number() with partition by

Schema (PostgreSQL v10.0)

create table users(user_id integer, name varchar(10),   email varchar(20),  modified_date timestamp);

insert into users 
values
(1  ,'John',    '[e-mail]',  '2022-01-01'),
(1  ,'John',    '[e-mail]',  '2022-02-01');

---

Query #1

select user_id, email, name
from
(
SELECT user_id, name, email,row_number() over(partition by user_id order by modified_Date desc) as rnk
FROM users
  ) as t
  where rnk = 1;

user_id	email	name
1	[e-mail]	John

---

View on DB Fiddle