Demo, Sql Server - rextester

Demo

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 ) ))


run \| edit \| history \| help	0

Please log in to post a comment.