Skip to content
Advertisement

Select the row with the most recent modified date

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 ?

Advertisement

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',    '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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement