SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table t(id int, name varchar(100),loc char(5));
insert into t values 
(101,'Stainless','NY'),
(101,'Brass','MUM'),
(102,'','NY'),
(102,'Gold','GP'),
(103,'Silver','CN'),
(103,'Aluminium','US'),
(104,'Platinum','NY'),
(104,'Diamond','UK'),
(105,'','UK')
9 rows affected


select * from
(
SELECT *,
row_number() over(partition by id order by case when NAME='' then -1 when LOC='NY' AND NAME!='' then 1 else 0 end desc) as rn
FROM t 
)A where rn=1


id
name
loc
rn


101
Stainless
NY
1

102
Gold
GP
1

103
Silver
CN
1

104
Platinum
NY
1

105

UK
1