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