select query from one table to check same value exist in column, mysql select query

Question

I have one table and i want to check that for one column all value are same.

following is the entry in my table.

two column

rid,value
(1,1)
(1,1)
(2,1)
(2,0)
(2,0)
(3,0)
(3,0)
(3,0)

I want query which gives me rid 1 because all of its value is 1. all record for rid 1 has value 1 and rid 2 and 3 does not has all value as 1 so they should not be selected.

Answer 1

Using group by and having can get what you want:

    SELECT rid, value
    FROM my_table
    GROUP BY rid
    HAVING COUNT( distinct value) = 1 

UPDATE

According to the comment, filter the value will get the result:

SELECT *
FROM
    (
    SELECT rid, value
    FROM my_table
    GROUP BY rid
    HAVING COUNT( distinct value) = 1 
    ) AS T1
WHERE value = 1

If the values would only be 1 or 0, then you could do this trick:

    SELECT rid, value
    FROM my_table
    GROUP BY rid
    HAVING COUNT( * ) = SUM(value)