idipous idipous - 7 months ago 5
SQL Question

Finding duplicates in table with different sql queries yields different results

I was expecting both queries to give the same outcome. Why is this not the case?

select count(*) as c,number from numbers group by number having count(*) > 1;

select * from numbers as a INNER JOIN numbers as b
on a.number = b.number and a.id < b.id;


First query return 1661 results where as the second 1911.

Answer

Because the first query will always return one row per number.
The second may return more rows with the same number (if you have triplicates etc)

try the below to see the difference

SELECT a.number, count(*) FROM
(select * from numbers as a INNER JOIN numbers  as b 
   on a.number = b.number  and a.id < b.id) c
GROUP BY a.number
-- having count(*) > 1
-- ORDER BY COUNT(*) DESC
Comments