vendredi 2 janvier 2015

SQL(ite) Remove (and keep some) duplicates in table

Say I have a table, called tablex, as follows:



name|year
---------
Bob | 2010
Mary| 2011
Sam | 2012
Mary| 2012
Bob | 2013


Names appear at most twice. I want to remove from the table only those names that are repeated and have a difference of one year (in which case I want to keep the newer year).



name|year
---------
Bob | 2010
Sam | 2012
Mary| 2012
Bob | 2013


I have tried:



SELECT a.Name, a.Year, b.Year
FROM tablex AS a
LEFT JOIN tablex AS b
ON a.Name=b.Name AND (a.Year=b.Year OR b.Year-a.Year=1)
ORDER BY a.Name, a.Year


results in:



Name YearA YearB
1 Bob 2010 2010
2 Bob 2013 2013
3 Mary 2011 2011
4 Mary 2011 2012
5 Mary 2012 2012
6 Sam 2012 2012


Bob's and Sam's entries are correct, how can I restrict it further to only include Mary 2012 2012?


Aucun commentaire:

Enregistrer un commentaire