Show distinct values if column X is not null-CodePudding

I need to select rows with distinct ID value but selected row should have DOB value that is not NULL if it exists. If not, then just select row with NULL DOB (example below with ID = 2).

ID   First_Name    Last_Name   DOB
1    John          Smith       1/1/1990
1    John          Smith       NULL
2    Jane          Doe         NULL
3    Martin        Schultz     NULL
3    Martin        Schultz     12/10/1976
3    Martin        Schultz     NULL

CodePudding user response：

Aggregate by ID and names, then select the max value for the date of birth:

SELECT ID, First_Name, Last_Name, MAX(DOB) AS DOB
FROM yourTable
GROUP BY ID, First_Name, Last_Name;

CodePudding user response：

Another alternative is row_number()

SELECT * FROM
(Select ID, First_Name,    Last_Name,   DOB
 row_number() over (PARTITION BY 
 ID, First_Name,    Last_Name
 ORDER BY 
 CASE WHEN DOB IS NOT NULL
 THEN 1 ELSE 0 END DESC) RN 
 from
 TABLE) WHERE RN =1;