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
Libros
Demo
sql
count events by 2 shift cycles using derived table
Manish_Sql_updated
Libros
Hospital
MOVIE TIME
Create MS SQL Server CUSTOMERS Table
Primeiro