CODEWITHSUNDEEP
sql-servernull
Adhitya Sanusi
Adhitya Sanusi

Reputation: 119

Remove Null if There are Other Non-Null Values

How do I remove null value when there is other non-null value for a specific CaseNumber?

Below is the raw data (Table 1)

CaseNumber |  Date
-----------|-----------
A          | NULL
A          | 08/11/2017
B          | 07/11/2017
B          | 06/11/2017
C          | NULL
C          | NULL
D          | NULL
F          | 05/11/2017
F          | NULL
F          | 04/11/2017
G          | 03/11/2017
G          | NULL

Below is the result that I want. 

CaseNumber |  Date
-----------|-----------
A          | 08/11/2017
B          | 07/11/2017
B          | 06/11/2017
C          | NULL
D          | NULL
F          | 05/11/2017
F          | 04/11/2017
G          | 03/11/2017

I'm using SQL server 2012.

Upvotes: 1

Views: 508

Answers (2)

Richard Wеrеzaк
Richard Wеrеzaк

Reputation: 1561

This was answered in a similar question here.

Easiest way to eliminate NULLs in SELECT DISTINCT?

In your case:

SELECT DISTINCT * FROM Table1
WHERE Date IS NOT NULL OR CaseNumber IN (
  SELECT CaseNumber FROM Table1
  GROUP BY CaseNumber HAVING MAX(Date) IS NULL)

Upvotes: 3

akuiper
akuiper

Reputation: 214957

You might select distinct CaseNumber left join all records where Date is not null:

;with not_null as (
  select * from t
  where date is not null
), unique_case as (
  select distinct casenumber 
  from t
)
select unique_case.CaseNumber, not_null.Date from unique_case
left outer join not_null
on unique_case.CaseNumber=not_null.CaseNumber

Here is the fiddle with fake data.

Upvotes: 1

Related Questions