Demo, Sql Server - rextester

Language: Layout:

CREATE TABLE #Table1
    ([Type] int, [SubType] varchar(3), [Flag] varchar(1), [Value] int)
;
    
INSERT INTO #Table1
    ([Type], [SubType], [Flag], [Value])
VALUES
    (123, 'A1', 'Y', 101),
    (123, 'A2', 'Y', 102),
    (123, 'A3', 'Y', 103),
    (124, 'A4', 'N', 104),
    (124, 'A5', 'N', 105),
    (124, 'A6', 'N', 106),
    (125, 'A7', 'Y', 107),
    (125, 'A8', 'Y', 108),
    (125, 'A9', 'N', 109),
    (125, 'A10', 'N', 110)
;

;WITH cte
		AS (SELECT Count(1)OVER(partition BY [Type]) cnt,*
			FROM  (SELECT DISTINCT [Type],
								[Flag],
								Min([Value])OVER(partition BY [Type])         AS min_val,
								Min([Value])OVER(partition BY [Type], [Flag]) AS min_fl_val
				FROM   #Table1) a)
SELECT *
FROM   #Table1 a
WHERE  EXISTS (SELECT 1
				FROM   cte b
				WHERE  a.Type = b.Type
						AND ( ( cnt = 1
								AND Flag = 'y'
								AND b.min_val = a.Value )
								OR ( cnt = 1
									AND Flag = 'N' )
								OR ( cnt > 1
									AND a.Flag = b.Flag
									AND a.Value = b.min_fl_val ) ))

View schema
Execution time: 0,03 sec, rows selected: 6, rows affected: 10, absolute service time: 0,17 sec