CODEWITHSUNDEEP
sqlsql-server
user2961127
user2961127

Reputation: 1143

Get distinct records from table

I have data table giving following results:

number  code
1        A
1        B
1        A
2        B
2        A
3        A
4        B

How do i find distinct number from table having code as A. The number should only have code A in table and not have B in table.
For the above table: My answer would be 3. As it has only code A.

Upvotes: 0

Views: 28

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you only want A, one method is aggregation:

select t.number
from t
group by t.number
having min(code) = 'A' and max(code) = 'A';

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

SELECT DISTINCT a.number 
FROM TableName a
WHERE a.Code = 'A'
AND NOT EXISTS (Select 1 
                FROM TableName b 
                WHERE a.number = b.number 
                 AND  b.Code = 'B')

Upvotes: 0

Related Questions