identify rows with not null values in sql

Question

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.

Gordon Linoff · Accepted Answer · 2017-02-22 18:53:18Z

3

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

answered Feb 22, 2017 at 18:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2017-02-22 18:54:01Z

2

A simple way is:

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

answered Feb 22, 2017 at 18:54

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

2 Comments

JasonInVegas Over a year ago

Using 'not in' clauses while a 'simple way' is typically not well-performing....an outer-join to the collection of null-status Ids is preferred.

Bohemian Over a year ago

@jason this is easier to understand and unless the data volume is massive this will perform just fine. Often the optimiser will convert this to a join anyway, so performance can be the same as a coded join.

Dave Espionage · Accepted Answer · 2017-02-22 18:59:44Z

0

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'

answered Feb 22, 2017 at 18:59

Dave Espionage

1362 silver badges7 bronze badges

Comments

J_Arthur · Accepted Answer · 2017-02-22 19:02:17Z

0

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);

answered Feb 22, 2017 at 19:02

J_Arthur

751 silver badge7 bronze badges

Comments

JasonInVegas · Accepted Answer · 2017-02-22 19:07:32Z

0

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

answered Feb 22, 2017 at 19:07

JasonInVegas

3912 silver badges10 bronze badges

Collectives™ on Stack Overflow

identify rows with not null values in sql

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related