請先確認 SQL Server 是否有區分大小寫，
否則要加入 Collate SQL_Latin1_General_CP1_CS_AS

CREATE TABLE TableX
(ID varchar(20),
NO varchar(20),
DIA varchar(20),
YN varchar(20) Collate SQL_Latin1_General_CP1_CS_AS
);

INSERT INTO TableX 
VALUES
('3201-20210103001','1','A','y'),
('3201-20210103001_002','1','A','Y'),
('3201-20210103001','2','A','y'),
('3201-20210103001_002','2','B','Y'),
('3201-20210103001','2','B','Y'),
('3201-210104001-ari','1','A','y'),
('3201-210104001_01','1','A','N'),
('3201-210104001','1','A','Y'),
('3201-210104001','1','B','N'),
('3201-210104001_02','1','B','N'),
-- 額外加入
('3201-210104003_03','1','B','n'),
('3201-210104004_04','1','B','Y'),
('3201-210104004_04','1','B','Y'),
('3201-210104004_04','1','B','y'),
('3201-210104004_04','1','B','N'),
('3201-210104004_04','1','B','y'),
('3201-210104004_04','1','B','Y'),
('3201-210104004_04','1','B','N'),
('3201-210104004_04','1','B','n')
;

SELECT ID,NO,DIA,YN
FROM (
SELECT ID,NO,DIA,YN,
ROW_NUMBER() OVER (PARTITION BY ID,NO,DIA 
ORDER BY ID,NO,DIA,
CASE WHEN YN='N' THEN 1
     WHEN YN='y' THEN 2
     WHEN YN='Y' THEN 3 
     ELSE 4 END) AS NUM
FROM (
SELECT 
d.vals.value('(/TR/TD)[1]','VARCHAR(20)') +'-'+
d.vals.value('(/TR/TD)[2]','VARCHAR(20)') 'ID',
NO,DIA,YN
FROM (
SELECT CAST('<TR><TD>' + REPLACE(REPLACE(ID,'_','-'), '-', '</TD><TD>') + '</TD></TR>' AS xml) vals,
NO,DIA,YN
FROM TableX) AS d
) AS M
) AS P
WHERE NUM=1

Demo