Danny David Leybzon Danny David Leybzon - 5 months ago 12
SQL Question

How do I generate a table of IDs which have only one attribute each?

I have a table that looks like this

id attribute
1 a
1 a
2 b
2 a


And I want to collect all of the IDs which have ONLY attribute a. So in the example case:

id
1


My initial thought was to use a
where
, but that would return:

id
1
1
2


Because 2 also has an "a" attribute in one instance.

P.S. I realize the phrasing of the title is ambiguous; maybe there's a better term than attribute to use in this case?

Answer

ohh I just saw hive but this is pretty standard sql give it a try.

SELECT
    ID
FROM
    TABLENAME
GROUP BY
    ID
HAVING
    COUNT(DISTINCT attribute) = 1

Having is like a where statement after the GROUP BY aggregation has occurred.