Select unique values based on other column value

Question

Hi Team - I am trying to return the rows below the scenario

SQL 2014:

create table TableK
(FirstName varchar(10),
LastName varchar(10),
ID int,
Date date,
let1 varchar(20),
let2 date,
documents varchar(20)
)

insert into TableK values
('Ava','Tom',1619,'02/10/2021', NULL, NULL, 'Income, Insurance'),
('Ava','Tom',1619,'02/10/2021', NULL, NULL, ''),
('Don','Joe',736,'01/07/2021',NULL, NULL, 'Income'),
('Don','Joe',736,'01/07/2021', NULL, NULL, ''),
('baby','shark',5086,'01/07/2021', NULL, NULL, '')

If ID ( example:1619 and 736) has more than one row then the query should return only with the documents column is not null/blank
if ID (5086) has a single row then no changes, display whatever the row as it is.

Output
:
('Ava','Tom',1619,'02/10/2021', NULL, NULL, 'Income, Insurance')
('Don','Joe',736,'01/07/2021',NULL, NULL, 'Income')
('baby','shark',5086,'01/07/2021', NULL, NULL, '')

Answer 1

Hi @kkran ,

Please refer below:

;with cte as (  
select FirstName,LastName,id,date,let1,let2,documents  
,ROW_NUMBER() over (partition by FirstName,LastName,id,date,let1,let2 order by documents desc) rn  
 from TableK )  
 select FirstName,LastName,id,date,let1,let2,documents   
 from cte where rn=1  

Output:

FirstName	LastName	id	date	let1	let2	documents  
Ava	Tom	1619	2021-02-10	NULL	NULL	Income, Insurance  
baby	shark	5086	2021-01-07	NULL	NULL	  
Don	Joe	736	2021-01-07	NULL	NULL	Income  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.