user634545 user634545 - 5 months ago 18
SQL Question

SQL Select distinct column values

Lets say I have a MySQL table that has the following entries;

ID, PRODUCTID
95, 2
95, 2
95, 1
96, 1
102, 1
102, 5
95, 5


How do I select the PRODUCTID(s) that only have one distinct ID-column value.

The result I want to get back is:

PRODUCTID
2


I have tried something in the lines of;

SELECT PRODUCTID
FROM `TABLE`
GROUP BY ID, PRODUCTID
HAVING COUNT(*) = 1;

Answer

Group by productid and then use a count(distinct id) to count unique ids

SELECT PRODUCTID
FROM your_table
GROUP BY PRODUCTID
HAVING COUNT(distinct ID) = 1