MySQL: finding duplicate / conflicting rows
Well, I know there are many questions already in this question, but they do not address my problem.
I have the following table
id name link year
1 t1 x1 2013
2 t5 x2 1990
3 t3 x1 2013
4 t2 x9 2011
Now I want to get duplicate records combined with two columns and the result should be
id name link year
1 t1 x1 2013
3 t3 x1 2013
here is what i have tried so far
SELECT * FROM table GROUP BY `link`, `year` HAVING COUNT(*) > 1
and it only returns one (second) row, so your help would be much appreciated.
+3
source to share
1 answer
You can find a combination of references and year using an aggregation for which multiple rows exist. You will need to join it with your original table to get all the rows for that link and years.
select *
from table t
join (
select link, year
from table
group by link, year
having count(*) > 1
) t2 using (link, year);
+3
source to share