Skip to content

Select the row with the most recent modified date

I have this table:

user_id name email modified_date
1 John 2022-01-01
1 John 2022-01-02
1 Lucy 2022-01-03
2 Joey 2021-12-24
3 Mike 2022-01-01
3 Mary 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 Lucy
2 Joey
3 Mary

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

Can somebody help me to sort this out ?



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 
(1  ,'John',    '',  '2022-01-01'),
(1  ,'John',    '',  '2022-02-01');

Query #1

select user_id, email, name
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 John

View on DB Fiddle

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