1

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.

Satish
  • 1,012
  • 2
  • 15
  • 32

2 Answers2

2

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)
Jaugar Chang
  • 3,176
  • 2
  • 13
  • 23
  • Thanks for your reply, but this doesn't gives me required result, As you see in my table value, it has only 1 and 0 as value. so even if for rid 3 all values are 0 it should not return 3 in result. result should be only one row as per my record and that should be rid 1. – Satish Dec 08 '14 at 08:45
0

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.

Mobasher Fasihy
  • 1,021
  • 2
  • 9
  • 17
  • Thanks for your reply, but this doesn't gives me required result, As you see in my table value it has only 1 and 0 as value.. so i want rid for value 1 only.. in your query it gives me result of all records whose value is same.. Please check with the data i have given... – Satish Dec 08 '14 at 08:43