Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
lab5
exp7
STACK
TEST-A3
gdfgfd
Escola
sql_fb
Delete double entries from a table without primary key
#Temp Table fill
Show slowest queries