CODEWITHSUNDEEP
sqloracle-database
Pat
Pat

Reputation: 613

identify rows with not null values in sql

How to retrieve all rows having value in a status column (not null) group by ID column.

Id      Name    Status
1394    Test 1  Y
1394    Test 2  null    
1394    Test 3  null    
1395    Test 4  Y
1395    Test 5  Y

I wrote like select * from table where status = 'Y'. It brings me 3 records, how to add condition to bring in only last 2? the 1394 ID have other 2 records, which status is null.

Upvotes: 2

Views: 3544

Answers (5)

JasonInVegas
JasonInVegas

Reputation: 391

Here are some possible solutions, because I am unclear on exactly what you want as output:

Select Id, Name, Status from table where status is not null;

results in 3 rows:

 Id      Name    Status
1394    Test 1    Y 
1395    Test 4    Y
1395    Test 5    Y


Select Id, count(*) as anAmt from table where status is not null group by Id;  
    /* only retrieves counts per Id */

results in 1 row for each Id:

Id         anAmt
1394         1
1395         2

Upvotes: 0

J_Arthur
J_Arthur

Reputation: 75

If I am reading this correctly you want to bring in the ID for a grouping that never has a NULL status value:

I would use a subquery with a not-exist:

SELECT DISTINCT ID FROM mytable WHERE status IS NULL;

Then filter IDs that do not exist in that list:

SELECT * FROM mytable WHERE id NOT IN (SELECT DISTINCT ID FROM mytable WHERE status IS NULL);

Upvotes: 0

Dave Espionage
Dave Espionage

Reputation: 136

The existing query "where status = 'Y'" will bring you not null by definition.

If you are trying to get grouped results, a "GROUP BY id" clause will achieve this, which will also require putting id in the select explicitly instead of "*".

Example: SELECT id, COUNT(id) from table where status = 'Y'

Upvotes: 0

Bohemian
Bohemian

Reputation: 425033

A simple way is:

select * from mytable
where status = 'Y'
and id not in (select id from mytable where status is null)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want to select groups where the status is only y, you can do:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and
                        (t2.Status <> 'Y' or t2.status is null)
                 );

If you only want the ids, I would use group by and having:

select id
from t
group by id
having min(status) = 'Y' and max(status) = 'Y' and count(*) = count(status);

The last condition checks for no NULL values.

You could also write:

having min(status = 'Y' then 1 else 0 end) = 1

Upvotes: 3

Related Questions