Member Avatar for kgizo

Good day

I have a table which I have already uploaded a list on however I want to return all the records which are duplicated and have a year between 2014 and 2015, i.e if I have a table like this

Year   IDNumber   FirstName   Surname  Email
2014  8809254      Tom               Jackson    jtr@gmail.com
2014  8809254      Tom               Jackson    ytr@gmail.com 
2015  8809254      Tom               Jackson    fyj@gmail.com

I only want to return duplicated records that have a entry in 2014 and 2015, so if this person had entries in 2014 only, then the query will discard this person. What I want to achieve with this is to get all the latest entries for duplicated records.

My query that checks for duplicates is provided below:

select year,idnumber,firstname,surname,email from MainList where ListID='NonEnCC7Jun16'
and idnumber in (
select idnumber  
from Mainlist where ListID='NonEnCC7Jun16'   
group by idnumber,firstname,surname  
having count(*) > 1 
)

If my query can be amended in such a way that if the years exist for that particular then all older records will be subsequently deleted then that would be even better.

Thanks in advance I hope my question is clear enough.

  • 2 Contributors
  • 6 Replies
  • 440 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

Brief summary and correction of earlier points: duplicates are defined here as the combination of idnumber, firstname and surname (as clarified by ). was right to point out that DISTINCT does not enforce “has both 2014 and 2015” and that SELECT does not delete rows — the solution is to first identify people who have rows in both years, then either select their rows or delete the older rows explicitly.

A safe two-step pattern — first identify the people with entries in both years, then show all rows for them — looks like this:

WITH BothYears AS (
  SELECT idnumber, firstname, surname
  FROM MainList
  WHERE ListID = 'NonEnCC7Jun16'
  GROUP BY idnumber, firstname, surname
  HAVING SUM(CASE WHEN [Year] = 2014 THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN [Year] = 2015 THEN 1 ELSE 0 END) > 0
)
SELECT m.*
FROM MainList m
JOIN BothYears b
  ON m.idnumber = b.idnumber
 AND ISNULL(m.firstname,'') = ISNULL(b.firstname,'')
 AND ISNULL(m.surname,'') = ISNULL(b.surname,'')
WHERE m.ListID = 'NonEnCC7Jun16';

To remove the older rows while keeping the latest (for those people), wrap the DELETE in a transaction and capture deleted rows with OUTPUT for review before committing:

BEGIN TRAN;

WITH BothYears AS ( ...same definition as above... )

DELETE m
OUTPUT deleted.*
FROM MainList m
JOIN BothYears b
  ON m.idnumber = b.idnumber
 AND ISNULL(m.firstname,'') = ISNULL(b.firstname,'')
 AND ISNULL(m.surname,'') = ISNULL(b.surname,'')
WHERE m.ListID = 'NonEnCC7Jun16'
  AND m.[Year] < (
    SELECT MAX([Year])
    FROM MainList
    WHERE idnumber = m.idnumber AND firstname = m.firstname AND surname = m.surname AND ListID = 'NonEnCC7Jun16'
  );

-- Inspect OUTPUT rows, then COMMIT or ROLLBACK as appropriate.

Notes and caveats: backup the table before deleting; run the SELECT variant first to confirm rows. If multiple rows exist for the same year, use ROW_NUMBER() partitioned by idnumber, firstname, surname and ordered by Year DESC (and a timestamp or surrogate key) to deterministically keep one row. Also verify that idnumber truly identifies a person — if not, include additional columns in the grouping to avoid accidental removals.

Recommended Answers

All 6 Replies

Member Avatar for kgizo

Distinct wont give me the results I am looking for at the moment since I want a comparison between 2014 and 2015 i.e if a person has entries for 2014 and 2015 then I want the results to be returned so that I can remove the 2014 entries on excel and then write an exclusion suquery

some thing like

and year in (2014,2015)

this would be inserted in the query above and all the results will be returned of all records that have entries for 2014 and 2015, so if a person only has an entry for 2014 then the results wontr be returned.

Member Avatar for kgizo

To rephrase I want all the duplicated records which have a 2014 and 2015 entry.

Member Avatar for rproffitt

Sorry but the example you gave are not duplicates. The first and last name match but the email differs so that's not a duplicate unless that's your definition of a duplicate.

In the real world, there are many many John Smith's.

Member Avatar for kgizo

Oh sorry rproffitt the duplicate is with the idnumber firstname and surname.

Member Avatar for rproffitt

I apologise here but "select" does not delete. Delete deletes. What I think you need to focus on is what you are trying to do here and practice your SQL commands till you nail it.

If I wanted to delete, well that's an entirely different command.
If you wanted to only select a specifc record set, then you work on that.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.