How to select both row_number and count over partition?

Question

I need to find duplicate record (with master record id and duplicate record ids):

select ciid, name from (
select ciid, name, row_number() over (
  partition by related_id, name order by updatedate desc) rn
) where rn = 1;

This gives me the master record IDs, but it also includes records without duplicates.

If I use

select ciid, name from (
select ciid, name, row_number() over (
  partition by related_id, name order by updatedate desc) rn
) where rn > 1;

This gets me all the duplicate records, but not the master record.

I was wishing if I do something like:

select ciid, name from (
select ciid, name, row_number()  over (
    partition by related_id, name order by updatedate desc
  ) rn, count(*)  over (
    partition by related_id, name order by updatedate desc
  ) cnt
) where rn = 1 and cnt > 1;

But I was worried about the performance, or even is it actually doing what I want.

How do I get the master record only for the ones with duplicates? Please note that name is not unique column. Only ciid is unique.

Answer 1

I ended up using similar query in my question:

select ciid, name from (
select ciid, name, row_number()  over (
    partition by related_id, name order by updatedate desc
  ) rn, count(*)  over (
    partition by related_id, name desc
  ) cnt
) where rn = 1 and cnt > 1;

Works surprisingly well. The master record is where rn = 1 and duplicates are where rn > 1. Make sure count(*) over (partition ..) cannot have order by clause.