CODEWITHSUNDEEP
sqloracle-database
Piotrek Leśniak
Piotrek Leśniak

Reputation: 83

Find rows that have both boolean in a column

I have oracle11 table like this:

id          name       have_child
----------- ---------- ------------
1           Alison     N
2           Mary       N
3           Meg        Y
4           Mary       N
5           Meg        N

where have_child is probably Boolean = Y/N. I want to do query to list wrong behavior where one name can be Y and N - like Meg:

id          name       have_child
----------- ---------- ------------
3           Meg        Y
5           Meg        N

As a result I want to list entire rows.

I do not want to list proper duplicates - like Mary:

id          name       have_child
----------- ---------- ------------
2           Mary       N
4           Mary       N

I know how to count particular names and list what names appears more than 1 time like this:

SELECT name from table
GROUP BY name
HAVING COUNT(*)>1;

Upvotes: 1

Views: 165

Answers (1)

Aleksej
Aleksej

Reputation: 22949

This could be a way:

select id, name, have_child
from (
    select t.*,
           count(distinct have_child) over (partition by name) as num
    from yourTable t
    )
where num > 1

The inner query simply lists all the records of the table, adding a column which gives the number of different values of have_child for the same name. The external one simply filters for rows in which this number is greater than 1.

Upvotes: 2

Related Questions