Get ids only where one id is null and other isn't 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
 +------+-----+
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) &gt; 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