I have the following data:
ID Site 2 NULL 2 32 3 6 4 7 8 12 8 13 9 14 9 14
Result should be:
ID Site 2 NULL 2 32 8 12 8 13
Note that the result find unique combinations of ID and Site that repeat more than once for a given ID.
I did the following query but does not return the result:
select distinct id, site from Table1 group by id, site having count(*) > 1 order by id
Advertisement
Answer
SELECT ID, site FROM table1 WHERE ID IN ( SELECT ID FROM ( SELECT ID ,site FROM table1 GROUP BY ID ,site ) x GROUP BY ID HAVING count(*)>1 )
See: DBFIDDLE
- The
SELECT ID, site FROM table1 GROUP BY ID, site
will select the distinct values. - Then, using
HAVING count(*) > 1
, only the IDs that appear more than once are filtered.
P.S. You should try to avoid using DISTINCT
and GROUP BY
in one query. It makes life so much more complicated when you do that … ð