Validation Column for Unique Value

Question

My table:

id attribute 1 2 1 2 2 3 2 4 5 1 5 1 6 3 6 3 6 5 

Now I only want to output those id using attribute if the attribute is the same for each id .

In this sample table, the output will be

 id attribute 1 2 5 1 

Answer 1

You can use this approach:

 SELECT DISTINCT id, attribute FROM test t1 WHERE (SELECT count(DISTINCT attribute) FROM test t2 WHERE t2.id = t1.id) = 1 

A better approach might be:

 SELECT DISTINCT t1.id, t1.attribute FROM test t1, ( SELECT id, count(DISTINCT attribute) COUNT FROM test GROUP BY id HAVING COUNT = 1 ) t2 WHERE t1.id = t2.id 

Answer 2

 SELECT id, MIN(attribute) AS attribute FROM test GROUP BY id HAVING COUNT(DISTINCT attribute) = 1 ; 

or

 SELECT id, MIN(attribute) AS attribute FROM test GROUP BY id HAVING MIN(attribute) = MAX(attribute) ; 

I expect the latest version will be quite efficient with an index on (id, attribute)

Answer 3

Well, I would use the following approach:

  SELECT DISTINCT ta.id, ta.attribute FROM test ta LEFT JOIN test tb ON ta.id = tb.id AND ta.attribute <> tb.attribute WHERE tb.id IS NULL; 

... since I usually try to at least try to replace nested queries with joined tables. Of course, if your table is small, it does not really matter.