none
Query to look for a column in particular group has null RRS feed

  • Question

  • Hi

    create table #temp(id int, specific int null)
    insert into #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10), (3, 10), (3, null)

    -- Select rows where a row has specific column has null this should return 1 and 3 
    -- Select rows where a row doesn't have speicifc column value hasn't null

    drop table #temp

    TIA

    Friday, January 3, 2020 5:09 PM

All replies

  • create table #temp(id int, specific int null)
    insert into #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10), (3, 10), (3, null)
    
    --return id = 1,3 
    select * from #temp
    Where specific  is null
    
    --return id = 1,2,3
    select * from #temp
    Where specific  is not null
    
    
    --return id = 2
    select * from #temp t
    Where not exists (select 1 from #temp t1 where t.id=t1.id 
    and specific  is  null)
    
    
    drop table #temp

    Friday, January 3, 2020 6:12 PM
    Moderator
  • Slightly changed table values:

    insert into #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10),  (3, 10), (3, null), (4, null), (5, null)

    -- Select all ids where all of specific column values are null. this should return 4 and 5

    -- Another one select ids where at least one of specific column value is not null. This should return 1, 2, and 3.

    TIA

    Friday, January 3, 2020 7:18 PM
  • -- 1. 
    SELECT * 
    FROM #temp
    WHERE id NOT IN (
    SELECT id 
    FROM #temp
    WHERE specific IS NOT NULL
    GROUP BY id
    );
    
    -- 2.
    SELECT id 
    FROM #temp
    WHERE specific IS NOT NULL
    GROUP BY id


    A Fan of SSIS, SSRS and SSAS

    Friday, January 3, 2020 7:29 PM
  • Slightly changed table values:

    insert into #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10),  (3, 10), (3, null), (4, null), (5, null)

    -- Select all ids where all of specific column values are null. this should return 4 and 5

    -- Another one select ids where at least one of specific column value is not null. This should return 1, 2, and 3.

    TIA

    create table #temp(id int, specific int null)
    insert into #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10),  (3, 10), (3, null), (4, null), (5, null)
    --Query1 
    -- Select all ids where all of specific column values are null. this should return 4 and 5
     -- returns 4, 5
     ;with mycte as (
     select id 
     ,sum(case when specific  is not null then 1 else 0 end) Over(partition by id) sum1
     from #temp
     )
     select id from mycte
     Where sum1=0
    
    
    
    --query2
    --return id = 1,2,3
    -- Another one select ids where at least one of specific column value is not null. This should return 1, 2, and 3.
    select id from #temp
    Where specific  is not null
    
    
     
    
    drop table #temp


    • Proposed as answer by Lily Lii Tuesday, January 7, 2020 8:17 AM
    Friday, January 3, 2020 7:37 PM
    Moderator
  • You apparently were asleep when her SQL class discussed keys. By definition, a key cannot have nulls in it. This is not an option; this is the definition and it's usually given in the first week of class. But the crap you've posted cannot have a single column key, or a two column key and you've only got two columns in this non-table. Would you like kludge so you can keep being a really bad SQL programmer? 

    There is no such thing as a generic "id" in RDBMS because an identifier must identify something in particular. The word "specific_something" is an adjective and not a noun. Because keys cannot have nulls in them, we have to use a unique constraint in the DDL that you posted. Here is a correction

    CREATE TABLE  Foobar  -- not a table!!
    (generic_id INTEGER NOT NULL,
     specific_something INTEGER,
     UNIQUE (generic_id, specific_something));

    INSERT INTO Foobar 
    VALUES (1, 10), (1, 20), (1, NULL), (2, 20), (2, 10), (3, 10), (3, NULL);

    -- Select rows where a row has specific_something column IS NULL this should return 1 and 3 
    -- Select rows where a row doesn't have specific column value hasn't NULL

    SELECT generic_id
      FROM Foobar
    GROUP BY generic_id
    HAVING COUNT(*) <> COUNT(specific_something); 

    Little trick here is that count (*) returns the count of rows in the grouping but if you have count (<expression>) drops the nulls before returning.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, January 3, 2020 7:46 PM
  • Hi bluepink,

    create table #temp(id int, specific int null)
    insert #temp values (1, 10), (1, 20), (1, null), (2, 20), (2, 10),  (3, 10), (3, null), (4, null), (5, null)
    
    -- Select all ids where all of specific column values are null. this should return 4 and 5
    ;with cte1 as(
    select *,row_number()over(partition by id order by id)
             +case when specific is not null then 1 else 0 end as rn
    from #temp)
    select distinct id from cte1
    where rn=1
    
    -- Another one select ids where at least one of specific column value is not null. This should return 1, 2, and 3
    ;with cte1 as(
    select *,row_number()over(partition by id order by id)
             +case when specific is not null then 1 else 0 end as rn
    from #temp)
    select distinct id from cte1
    where rn<>1

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Tuesday, January 7, 2020 8:17 AM
    Monday, January 6, 2020 4:37 AM
  • Hi bluepink,

    May I ask that if your issue has been solved? Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 7, 2020 8:18 AM