Select the row with the most recent modified date

Question

I have this table:

user_id	name	email	modified_date
1	John	a@gmail.com	2022-01-01
1	John	b@gmail.com	2022-01-02
1	Lucy	c@gmail.com	2022-01-03
2	Joey	d@gmail.com	2021-12-24
3	Mike	e@gmail.com	2022-01-01
3	Mary	f@gmail.com	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	c@gmail.com	Lucy
2	d@gmail.com	Joey
3	f@gmail.com	Mary

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

Can somebody help me to sort this out ?

Answer 1

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',    'a@gmail.com',  '2022-01-01'),
(1  ,'John',    'b@gamil.com',  '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	b@gamil.com	John

---

View on DB Fiddle