How to compare two rows in a single table

I have staging table with employee with columns like firstname, middlename, lastname, department, effectivedate, canceldate and processdate

I have to compare each row with the other rows for finding duplicates, if two rows match than I have to select one which has greater开发者_开发知识库 process date.

I am using a CTE with the Dense_rank function for finding duplicates, but I am not getting how to compare rows in same table.

Thanks in advance..

This will find the duplicate record with greater process date

select s1.*
from staging s1
join staging s2
    on s1.firstname = s2.firstname
    and s1.middlename = s2.middlename
    and s1.lastname = s2.lastname
    and s1.department = s2.department
    -- compare other columns that make records "duplicates" as appropriate
    and s1.processdate > s2.processdate; -- this makes the s1 record the latest