SQL Year Comparison Query For Dupliacted Records

[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    [e-mail]
2014  8809254      Tom               Jackson    [e-mail] 
2015  8809254      Tom               Jackson    [e-mail]

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.

[rproffitt]

This looks like a job for "distinct" More at http://www.tutorialspoint.com/sql/sql-distinct-keyword.htm

[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.

[kgizo]

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

[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.

[kgizo]

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

[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.