CODEWITHSUNDEEP
sqloracle-database
Devin
Devin

Reputation: 1992

Selecting rows where there is repetition in columns

Let's say I have a table like below:

Name   Score
-----  ----- 
Don    3
Don    4
Don    0
Pat    3
Cat    5

How to write a query that will filter out Name where the score is 0? I.e.: In the above table, the query must not return the Name "Don" since one of the rows for Don contains 0. (I'm using Oracle db BTW.)

Upvotes: 1

Views: 60

Answers (3)

TommCatt
TommCatt

Reputation: 5636

This answer is similar, but if you want other ways:

select Name
from   T1
group by Name
having Min( Score ) > 0;

This assumes that Score cannot be negative which, from context, looks like a pretty safe assumption. It would still work if you wanted to omit anyone with a score of zero or less. If you wanted to omit only zero but still pass negative values, then change the test to having Min( Abs( Score )) > 0.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221370

The first solution that usually jumps to ones mind is something with EXISTS:

SELECT *
FROM players p1
WHERE NOT EXISTS (
    SELECT 1
    FROM players p2
    WHERE p1.Name = p2.Name
    AND p2.Score = 0
)

Here's a SQLFiddle showing it in action, returning

| NAME | SCORE |
|------|-------|
|  Cat |     5 |
|  Pat |     3 |

Upvotes: 0

juergen d
juergen d

Reputation: 204924

select name
from your_table
group by name
having sum(case when score = 0 then 1 else 0 end) = 0

Upvotes: 2

Related Questions