CODEWITHSUNDEEP
sqloracle-database
user2498741
user2498741

Reputation: 45

How do I display Rows in a table where all values but the first one for a column is null

So I am trying to pull rows from a table where there are more than one version for an ID that has at least one person for the ID that is not null but the versions that come after it are null.

So, if i had a statement like:

select ID, version, person from table1

the output would be:

ID  Version  Person
--  -------  ------
 1        1  Tom 
 1        2  null 
 1        3  null   
 2        1  null
 2        2  null   
 2        3  null
 3        1  Mary
 3        2  Mary
 4        1  Joseph
 4        2  null   
 4        3  Samantha

The version number can have an infinite value and is not limited.

I want to pull ID 1 version 2/3, and ID 4 Version 2.

So in the case of ID 2 where the person is null for all three rows I don't need these rows. And in the case of ID 3 version 1 and 2 I don't need these rows because there is never a null value.

This is a very simple version of the table I am working with but the "real" table is a lot more complicated with a bunch of joins already in it.

The desired output would be:

ID     Version     Person
--     -------     ------
1        2           null
1        3           null    
4        2           null

The result set that I am looking for is where in a previous version for the same ID there was a person listed but is now null.

Upvotes: 1

Views: 84

Answers (4)

dnoeth
dnoeth

Reputation: 60472

This should do what you want:

select id, version, person
from
 (
   select id, version, person,
      lag(person, 1) ignore nulls
      over (partition by id
            order by version) as x
   from table1
 ) dt
where person is null 
  and x is not null

Upvotes: 0

WoMo
WoMo

Reputation: 7256

You are seeking all rows where the person is not null and that id has null rows, and the not null person version is less than the null version for the same person id:

Edited predicate based on comment

with sample_data as
  (select 1 id, 1 version, 'Tom' person from dual union all
  select 1, 2, null from dual union all
  select 1, 3, null from dual union all
  select 2, 1, null from dual union all
  select 2, 2, null from dual union all
  select 2, 3, null from dual union all
  select 3, 1, 'Mary' from dual union all
  select 3, 2, 'Mary' from dual union all
  select 4, 1, 'Joseph' from dual union all
  select 4, 2, null from dual union all
  select 4, 3, 'Samantha' from dual)
select *
from sample_data sd
where person is null
and exists
  (select 1 from sample_data 
    where id = sd.id 
      and person is not null 
      and version < sd.version);

/* Old predicate
    and id in
      (select id from sample_data where person is not null);
*/

Upvotes: 3

Ronnis
Ronnis

Reputation: 12833

I think this query translates pretty nicely into what you asked for?

List all the rows (R) where the person is null, but only if a previous row (P) with a non-null name exists.

select * 
  from table1 r
 where r.person is null
   and exists(
         select 'x'
           from table1 p
          where p.id = r.id
            and p.version < r.version
            and p.person is not null
       );

Upvotes: 3

SS781
SS781

Reputation: 2649

I believe the below should work.

select ID, listagg(version, ', ') within group (order by version) as versions
from table1 t1
where 0 < (select count(*) from table1 t1A where t1A.ID = t1.ID and t1A.version is not null)
and 0 < (select count(*) from table1 t1B where t1B.ID = t1.ID and t1B.version is null)
and person is null
group by ID

Upvotes: 0

Related Questions