Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
sql server dynamic 12 month time series and pivot with month year column names
Madelin
FIGURA5.2
MyDb
SQL Directives Order of Execution
Exam_2_Final
Fatch data in xml format
Insert multiple parent-child records
Find effective count of employees within date range for each calendar week
new problem SQL
Please log in to post a comment.