SQL select rows with only a certain value in them

Question

I have a table as such

Col 1 Col 2 Col 3
1       A     1
2       A     2
3       B     1
4       C     1
5       C     2
6       D     1

How do I only get unique rows which have Col 3 = 1?

I want to get rows 3 and 6 (Col 2 = B and D respectively). I do not want A nor C since they have Col 3 = 2 as well.

I've tried something along the lines of:

select col 2 from table group by col 2 having count(col 3) = 1

But that only brings up Col 2 for results so I'm uncertain if Col 3 contents = 1 or not.

EDIT: Sorry guys maybe I've not worded my question clearly. I want to get all of the rows of Col 2 which contain only Col 3 = 1 AND ONLY 1.

So if I tried WHERE Col 3= 1, it would return 4 rows because A has 1. But since A also has a row where Col 3 = 2, I do not want that, same for C. From this example table, I would want the end result to only show 2 rows, B and D.

My example table is an example, I actually have about 5000 rows to filter through, otherwise I'd do as you guys have suggested :)

t'is time to read an sql tutorial: http://www.w3schools.com/sql/default.asp — Denis de Bernardy, May 24 '11 at 12:44
Sorry guys, it's not as simple as it seems. Unless I'm missing something inferred from that SQL query by AllisonC. — MHTri, May 24 '11 at 12:52
It is not good to change whole question after a few minutes, that makes most of answers wrong. Better to ask new one. — Mihran Hovsepyan, May 24 '11 at 13:05
I have not changed my question, but it seems I've initially written it in such a a way that people misunderstood with their answers, so I needed to add clarification. I apologize if this has caused you issues. — MHTri, May 24 '11 at 13:08

Martin Smith · Accepted Answer · 2016-04-06T05:18:19.597

16

SELECT col2
FROM your_table
GROUP BY col2
HAVING MAX(col3) = 1 AND MIN(Col3) = 1

Or

SELECT a.col2 
FROM your_table a
WHERE a.col3=1 AND NOT EXISTS(SELECT *
                              FROM your_table b 
                              WHERE a.col2=b.col2 AND b.col3<>1)

edited Apr 06 '16 at 05:18

answered May 24 '11 at 13:00

Martin Smith

438,706
87
741
845

you could also do `HAVING AVG(col3) = 1` – Srivastav Jan 05 '16 at 14:15
Is the second one supposed to be `b.col3 <> 1` at the end? – 322896 Apr 06 '16 at 04:15

score 3 · Answer 2 · answered May 24 '11 at 12:46

3

What you are probably looking for is WHERE clause.

SELECT * FROM YouTable WHERE col3 = 1 AND col2 in ('B','D');

answered May 24 '11 at 12:46

Damian Leszczyński - Vash

30,365
9
60
95

score 2 · Answer 3 · answered May 24 '11 at 12:53

;with T ([Col 1], [Col 2], [Col 3]) as
(
select 1,       'A',     1 union all
select 2,       'A',     2 union all
select 3,       'B',     1 union all
select 4,       'C',     1 union all
select 5,       'C',     2 union all
select 6,       'D',     1
)


select *
from T
  left outer join 
    (
      select distinct [Col 2] 
      from T
      where [Col 3] <> 1
    ) as T2
  on T.[Col 2] = T2.[Col 2]
where T.[Col 3] = 1 and
      T2.[Col 2] is null

score 0 · Answer 4 · answered May 24 '11 at 12:55

0

It's a bit hard to know exactly what you're trying to get, but this is my best guess:

SELECT * FROM theTable WHERE col2 NOT IN
  (SELECT col2 FROM theTable WHERE col3 <> 1)

answered May 24 '11 at 12:55

ic3b3rg

14,629
4
30
53

score 0 · Answer 5 · answered May 24 '11 at 13:03

SELECT * FROM #temp t1
WHERE EXISTS 
(
select Col2 from #Temp t2 
WHERE t2.Col2 = t1.Col2
group by col2 
having count(col3) = 1
)

tested with MS SQL2008 and the following (so if my answer is not the correct one it may halp others test theirs...):

CREATE TABLE #temp 
( 
    Col1 INT, 
    Col2 CHAR(1),
    Col3 INT
)

INSERT INTO #Temp    
    (Col1, Col2, Col3)
SELECT    1,'A',1
UNION
SELECT    2,'A',2
UNION
SELECT  3,'B', 1
UNION 
SELECT 4,'C',1
UNION 
SELECT 5,'C',2
UNION 
SELECT 6,'D',1

SELECT * FROM #temp t1
WHERE EXISTS 
(
select Col2 from #temp t2 
WHERE t2.Col2 = t1.Col2
group by col2 
having count(col3) = 1
)

DROP TABLE #temp

SQL select rows with only a certain value in them

5 Answers5

Linked