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 email@example.com 2014 8809254 Tom Jackson firstname.lastname@example.org 2015 8809254 Tom Jackson email@example.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.