Rank() Over Partition By to rank over a table column to give me the older records

Question

THis is what i have - a query that retrieves all the Persons that have a duplicated email address believe rank over partition by should solve my problem (The filter is the Email Address)

SELECT a.Id, a.EmailAddress,a.UntilDate,a.CreatedOn,a.UserId
    INTO #GetEmployeesWithDuplicateEmails
    FROM Employee a
    INNER JOIN (SELECT                    
                    Employee.EmailAddress as EmailAddress                
                FROM Employee
                GROUP BY Employee.EmailAddress
                HAVING count(Employee.EmailAddress) > 1
                    ) b
    ON a.EmailAddress = b.EmailAddress
    ORDER BY a.Id

this is the output of the query Query Result

What i want - Query below retrieves the users that have duplicated EmailAddresses, i want to keep the most recent record by each email, if the email belongs to the same UserId of course, imagine that there are 5 duplicated emails, if i verify that those 5 duplicates belong to the same UserId i want to keep the newest record based on the CreatedOn field, the other 4 will be updated . I wanted to use rank over partition by, but you can advise me with a better scenario anyways here goes:

    SELECT          #GetEmployeesWithDuplicateEmails.*,
                RANK() OVER (
                PARTITION BY #GetEmployeesWithDuplicateEmails.CreatedOn
                 ORDER BY #GetEmployeesWithDuplicateEmails.CreatedOn DESC) createdon_rank               
INTO         #TableValuesToDelete 
FROM        #GetEmployeesWithDuplicateEmails 
INNER JOIN
(  
  (SELECT #GetEmployeesWithDuplicateEmails.[EmailAddress]
    FROM #GetEmployeesWithDuplicateEmails
    GROUP BY #GetEmployeesWithDuplicateEmails.[EmailAddress])
) as temp2 ON #GetEmployeesWithDuplicateEmails.[EmailAddress]=temp2.[EmailAddress]

update 
        #TableValuesToUpdate
SET
        #TableValuesToUpdate.EmployedUntilDate=getDate()
WHERE
        created_rank > 1

i want to retain the most recent record by each email if the email belongs to the same UserId, Imagine that there are 5 duplicated emails, if i verify that those 5 duplicates belong to the same UserId i want to keep the newest record based on the CreatedOn field .

Update: Just Updated my Partion By query but it still can't rank the displayed values

Sample data and desired results would help. Your first query is much too complicated. — Gordon Linoff, Mar 26 '20 at 11:44
It's almost always wrong to have the same expression in both your `PARTITION BY` and `ORDER BY` clauses - because by definition, all rows within each partition must have *exactly the same value* for that expression. — Damien_The_Unbeliever, Mar 26 '20 at 16:13

score 0 · Answer 1 · answered Mar 26 '20 at 11:47

0

If you want email addresses that belong to multiple users, you can use:

select e.*
from Employee e
where exists (select 1
              from Employee e2
              where e2.EmailAddress = e.EmailAddress and
                    e2.id <> e.id  -- or however you identify the same employee
            )
order by e.EmailAddress;

answered Mar 26 '20 at 11:47

Gordon Linoff

1,242,037
58
646
786

a new pic attached, since i want to retain the most recent record by each email if the email belongs to the same UserId, Imagine that there are 5 duplicated emails, if i verify that those 5 duplicates belong to the same UserId i want to keep the newest record based on the CreatedOn field – JoãoEs Mar 26 '20 at 12:15

Rank() Over Partition By to rank over a table column to give me the older records

1 Answers1