Sample Header Ad - 728x90

How to remove these duplicate records in an Oracle Database 12c?

0 votes
1 answer
170 views
I have these two tables:
messages(id primary key, message_date, created_at, ...)
user_messages(user_id, message_id references messages(id))
I have some duplicate rows in messages:
select user_id, message_date, count(*)
from messages inner join user_messages 
on messages.id = user_messages_message_id
group by user_id, message_date;

user_id | message_date | count(*)
1       | 2019-01-01   | 2
1       | 2019-02-01   | 3
1       | 2019-03-01   | 2
How can I remove such duplicates, only retaining one of them, for example the one that its created_at (not message_date) is the minimum?
Asked by orly (21 rep)
Nov 8, 2019, 03:24 AM
Last activity: Jul 4, 2025, 04:06 AM