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

CodePudding user response：

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 ...