2

I have a table as such where id's are repeating .

    +-------+------+
      ID      NUM
    +-------+------+
      1        50
      1        51
      2        52
      2        NULL
      3        NULL
      3        NULL 
      4        53
      4        54
      4        NULL
      5        NULL
      6        55
      7        NULL
    +-------+------+

I want to get table where i only have the ids which have both null and non null values in the column labelled (num)

like so

 +------+-----+
   ID     NUM
 +------+-----+
   2      52
   2      NULL
   4      53
   4      54
   4      NULL
 +------+-----+

  • Subject "Get ids only where one id is null and other isn't" do not match text "ids which have both null and non null values" (count of nulls is not specified). – Akina Jul 11 at 12:22
  • He want's ID's that have both NULL and non-NULL values. ID's that have all non-NULL or all NULL values are ok, it's when they are mixed that the OP wants to see them. – Jonathan Fite Jul 11 at 12:35
5
WITH cte AS ( SELECT id, 
                     num, 
                     COUNT(*) OVER (PARTITION BY id) total,
                     COUNT(num) OVER (PARTITION BY id) notnull
              FROM mytable )
SELECT id, num
FROM cte
WHERE notnull * (total - notnull) > 0

OR

WITH cte AS ( SELECT id, 
                     num, 
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num) mini,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num DESC) maxi
              FROM mytable )
SELECT id, num
FROM cte
WHERE mini IS NULL
  AND maxi IS NOT NULL
  • 1
    I think you need a partition by in your second example to make it work. – Jonathan Fite Jul 11 at 12:33
  • @JonathanFite Oh, such a carelessness! Great thanks, edited. – Akina Jul 11 at 12:36
  • Clever solution! It would be nice with a little explanation for those not familiar with Windows functions though – Lennart Jul 11 at 19:20
4
create table #test(
    ID  INT,
    NUM INT
)
INSERT #test
VALUES
      (1,        51),     
      (2,        52),     
      (2,        NULL),
      (3,        NULL),
      (3,        NULL), 
      (4,        53 ),     
      (4,        54 ),     
      (4,        NULL),
      (5,        NULL),
      (6,        55 ),     
      (7,        NULL)

SELECT t.ID, t.NUM
FROM #test t
    JOIN (
        SELECT ID
        FROM #test
        GROUP BY ID
        HAVING count(*) - count(NUM) = 1 -- one is NULL, others are not
            AND count(NUM) > 0 -- there is at least one not null value
    ) i ON i.ID = t.ID
ORDER BY t.ID 


DROP TABLE #test
  • You might concider count(*) - count(NUM) >= 1 ( in case of an id with several nulls) – Lennart Jul 11 at 19:27
  • My answer meets the title conditions if null's count doesn't matter then count(*) > count(NUM) – Denis Rubashkin Jul 12 at 7:08
3

Some other methods

Keep in mind that the table will be accessed 3 times

SELECT b1.ID, b1.Num
FROM mytable  b1
WHERE EXISTS
(
SELECT id 
from mytable   b2
where NUM is not null
AND b2.id=b1.id
INTERSECT
SELECT id 
from mytable  b3
where NUM is null
AND b3.id=b1.id
);

or

SELECT b1.ID, b1.Num
FROM mytable  b1
CROSS APPLY
(
SELECT id 
from mytable  b2
where NUM is not null
AND b2.id=b1.id
INTERSECT
SELECT id 
from mytable  b3
where NUM is null
AND b3.id=b1.id
)AS B2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.