CODEWITHSUNDEEP
mysqlsql
Terry Uhlang
Terry Uhlang

Reputation: 141

Check column for unique value

My table:

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

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

In this sample table, the output would be

id attribute
1  2
5  1

Upvotes: 7

Views: 2547

Answers (3)

Francisco Spaeth
Francisco Spaeth

Reputation: 23903

You could 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 could 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

Upvotes: 7

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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 would expect the last version to be quite efficient with an index on (id, attribute)

Upvotes: 8

raina77ow
raina77ow

Reputation: 106375

Well, I'd use the following approach instead:

     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;

... as I usually tend to at least trying to replace nested queries with joined tables. Of course, if your table is small, it won't matter much.

Upvotes: 1

Related Questions