Need sql help

  • i have two columns ID, Val with 

    IDVal
    1001X
    1002YY
    1003NNN
    1003Null
    1004 
    1005NULL
    1005ZZ
    1006NUll

    I need to have only non- null values if any ID has both null and non-null values + IDS which are with just null values.
    So expected data like below:

    IDVal
    1001X
    1002YY
    1003NNN
    1004 
    1005ZZ
    1006NUll

    Need to remove 1003,Null  1005, NUll rows as those ids already have non-null values

    Please advise the query.

  • WHERE Val IS NOT NULL gives you your expected results.  However, in your example, 1003 doesn't look like a NULL value - it is just the word "Null."  The query would be different if you're excluding the character string 'null' in addition to true NULLs (WHERE Val <> 'null'). 

    Also, your expected results don't match your description.  You say "I need to have only non- null values if any ID has both null and non-null values," which would mean in the results we should only get 1003 NNN and 1005 ZZ (assuming 1003 Null is a true NULL).  1001, 1002, 1004, and 1006 only have non-null values and should be excluded according to the description.

  • saptek9 - Wednesday, December 5, 2018 9:55 AM

    i have two columns ID, Val with 

    IDVal
    1001X
    1002YY
    1003NNN
    1003Null
    1004 
    1005NULL
    1005ZZ
    1006NUll

    I need to have only non- null values if any ID has both null and non-null values + IDS which are with just null values.
    So expected data like below:

    IDVal
    1001X
    1002YY
    1003NNN
    1004 
    1005ZZ
    1006NUll

    Need to remove 1003,Null  1005, NUll rows as those ids already have non-null values

    Please advise the query.

    Hi 

    I am not sure whether this would solve your problem but here is my solution.Let me know if you find it useful.

    SELECT
    Q.*
    FROM
    (
    SELECT
      S.ID
    , S.Val
    , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CASE WHEN Val IS NOT NULL THEN 0 ELSE 1 END) AS #s
    FROM
    #Sample S
    )Q
    WHERE
     Q.#s=1

  • ;WITH myTable as
    (
    SELECT *
      FROM (VALUES (1001, 'X'),
                   (1002, 'YY'),
                   (1003, 'NNN'),
                   (1003, NULL),
                   (1004, ''),
                   (1005, NULL),
                   (1005, 'ZZ'),
                   (1006, NULL)) T(Id,Val)
    )
    SELECT *
    FROM myTable t
    WHERE t.Val IS NOT NULL
    UNION ALL
    SELECT *
    FROM myTable t
    WHERE t.Val IS NULL
       AND NOT EXISTS(SELECT *
                        FROM myTable t2
                        WHERE t2.Id = t.Id
                           AND t2.Val IS NOT NULL)
     ORDER BY t.Id

    ;WITH myTable as
    (
    SELECT *
      FROM (VALUES (1001, 'X'),
                   (1002, 'YY'),
                   (1003, 'NNN'),
                   (1003, NULL),
                   (1004, ''),
                   (1005, NULL),
                   (1005, 'ZZ'),
                   (1006, NULL)) T(Id,Val)
    )
    SELECT *
    FROM myTable t
    WHERE t.Val IS NOT NULL
        OR( t.Val IS NULL
              AND NOT EXISTS (SELECT *
                                FROM myTable t2
                               WHERE t2.Id = t.Id
                                 AND t2.Val IS NOT NULL)
     ORDER BY t.Id

  • Jonathan AC Roberts - Thursday, December 6, 2018 7:34 AM

    ;WITH myTable as
    (
    SELECT *
      FROM (VALUES (1001, 'X'),
                   (1002, 'YY'),
                   (1003, 'NNN'),
                   (1003, NULL),
                   (1004, ''),
                   (1005, NULL),
                   (1005, 'ZZ'),
                   (1006, NULL)) T(Id,Val)
    )
    SELECT *
    FROM myTable t
    WHERE t.Val IS NOT NULL
    UNION ALL
    SELECT *
    FROM myTable t
    WHERE t.Val IS NULL
       AND NOT EXISTS(SELECT *
                        FROM myTable t2
                        WHERE t2.Id = t.Id
                           AND t2.Val IS NOT NULL)
     ORDER BY t.Id

    ;WITH myTable as
    (
    SELECT *
      FROM (VALUES (1001, 'X'),
                   (1002, 'YY'),
                   (1003, 'NNN'),
                   (1003, NULL),
                   (1004, ''),
                   (1005, NULL),
                   (1005, 'ZZ'),
                   (1006, NULL)) T(Id,Val)
    )
    SELECT *
    FROM myTable t
    WHERE t.Val IS NOT NULL
        OR( t.Val IS NULL
              AND NOT EXISTS (SELECT *
                                FROM myTable t2
                               WHERE t2.Id = t.Id
                                 AND t2.Val IS NOT NULL)
     ORDER BY t.Id

    Thank you so much Jonathan.

  • saptek9 - Thursday, December 6, 2018 7:43 AM

    Thank you so much Jonathan.

    II think sathwik.em91's solution might be the most efficient
    ;WITH myTable as
    (
    SELECT *
    FROM (VALUES (1001, 'X'),
                   (1002, 'YY'),
                   (1003, 'NNN'),
                   (1003, NULL),
                   (1004, ''),
                   (1005, NULL),
                   (1005, 'ZZ'),
                   (1006, NULL)) T(Id,Val)
    )
    SELECT Q.*
      FROM (SELECT S.ID,
                   S.Val,
                   ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CASE WHEN Val IS NOT NULL THEN 0 ELSE 1 END) AS #s
             FROM myTable S) Q
    WHERE Q.#s = 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply