December 5, 2018 at 9:55 am
i have two columns ID, Val with
| ID | Val |
| 1001 | X |
| 1002 | YY |
| 1003 | NNN |
| 1003 | Null |
| 1004 | |
| 1005 | NULL |
| 1005 | ZZ |
| 1006 | NUll |
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:
| ID | Val |
| 1001 | X |
| 1002 | YY |
| 1003 | NNN |
| 1004 | |
| 1005 | ZZ |
| 1006 | NUll |
Need to remove 1003,Null 1005, NUll rows as those ids already have non-null values
Please advise the query.
December 5, 2018 at 2:13 pm
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.
December 5, 2018 at 3:43 pm
saptek9 - Wednesday, December 5, 2018 9:55 AMi have two columns ID, Val with
ID Val 1001 X 1002 YY 1003 NNN 1003 Null 1004 1005 NULL 1005 ZZ 1006 NUll 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:
ID Val 1001 X 1002 YY 1003 NNN 1004 1005 ZZ 1006 NUll 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
December 6, 2018 at 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
December 6, 2018 at 7:43 am
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.
December 6, 2018 at 7:55 am
saptek9 - Thursday, December 6, 2018 7:43 AMThank 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