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)

Answer 2

You can do like this:

  CREATE TABLE my_table (
  id varchar(255),
  col_value varchar(255)
);
INSERT INTO my_table 
  VALUES
         ('1','1'),
         ('1','1'),
         ('2','1'),
         ('2','1'),
         ('2','1'),
         ('2','4'),
         ('3','1'),
         ('3','1');

Query for selection:

SELECT src.* FROM
(
    SELECT DISTINCT t1.* FROM my_table AS t1 
) AS src
WHERE src.id NOT IN(
        SELECT test.id
        FROM 
        (
             SELECT DISTINCT t1.* FROM my_table AS t1 
        ) AS test
        GROUP BY test.id
        HAVING COUNT(*) > 1
 )

fiddle here.