Filter rows based on certain conditions

Question

please help me out with a solution to this problem..

table is like this -

Type | SubType | Flag   | Value
--------------------------------
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

requirement is to select rows based on certain conditions - if all flags are Y for a particular type then select only row with lowest subtype, if all flags are N for a particular type then select all rows of that type , if flags for a type are combination of Y and N then select 2 rows for that type - one with lowest subtype of flag Y and one with lowest subtype of flag N.

So the output of above table should look like -

Type  | SubType | Flag   | Value
------------------------------ 
 123  |  A1     |   Y    |  101
------------------------------ 
 124  |  A4     |   N    |  104
------------------------------ 
 124  |  A5     |   N    |  105
------------------------------ 
 124  |  A6     |   N    |  106
------------------------------ 
 125  |  A7     |   Y    |  107
------------------------------ 
 125  |  A9     |   N    |  109

Sorry for bad table formats , Thanks in advance for looking into this.

Answer 1

Use a CTE to generate a summary for each type, then filter on it:

WITH summary AS (

        SELECT   [Type]

                ,SUM(CASE WHEN [Flag] = 'Y' THEN 1 ELSE 0 END) AS Count_Y
                ,SUM(CASE WHEN [Flag] = 'N' THEN 1 ELSE 0 END) AS Count_N

                -- This value will be NULL, if there are no rows with Y flag
                ,MIN(CASE WHEN [Flag] = 'Y' THEN CAST(SUBSTRING(SubType,2,99) AS INT) END) AS Lowest_Y_SubType
                -- This value will be NULL, if there are no rows with N flag
                ,MIN(CASE WHEN [Flag] = 'N' THEN CAST(SUBSTRING(SubType,2,99) AS INT) END) AS Lowest_N_SubType
        FROM dbo.tSO_41657760
        GROUP BY [Type]

)
SELECT *
FROM dbo.tSO_41657760 t
WHERE EXISTS (
        SELECT 1
        FROM summary
        -- Filter this inner table using Type from the outer table
        WHERE [Type] = t.[Type]
        AND 
        (
        -- All rows have N flag
        (Count_Y = 0)
        -- Select the lowest N and Y rows with lowest SubType values
        OR (CAST(SUBSTRING(SubType,2,99) AS INT) IN (Lowest_N_SubType, Lowest_Y_SubType))
        )
)

In the code above, we extract the digits from SubType to sort correctly. This ensures that A9 is smaller than A10 (otherwise, a plain alphabetic sort will yield incorrect results).

Answer 2

Here is one way

;WITH cte
     AS (SELECT Count(1)OVER(partition BY [Type]) cnt,*
         FROM  (SELECT DISTINCT [Type],
                                [Flag],
                                -- to find the first record when flag is Y
                                Min([Value])OVER(partition BY [Type])         AS min_val,
                                -- to find the first records of Flag Y and N for each type 
                                Min([Value])OVER(partition BY [Type], [Flag]) AS min_fl_val 
                FROM   Yourtable) a)
SELECT *
FROM   Yourtable 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 ) -- to find first record of Type when Flag is only Y
                             OR ( cnt = 1
                                  AND Flag = 'N' )  -- to pull all the record of Type when Flag is only N
                             OR ( cnt > 1
                                  AND a.Flag = b.Flag
                                  AND a.Value = b.min_fl_val ) )) -- to find first record for each flag in each type 

• Live Demo