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.

Gurwinder Singh · Answer 1 · 2017-01-15T07:22:27.400

You can make use of window functions like this:

with your_table (Type , SubType , Flag   , Value) as (
select 123  ,  'A1'     ,   'Y'    ,  101 union all
select 123  ,  'A2'     ,   'Y'    ,  102 union all
select 123  ,  'A3'     ,   'Y'    ,  103 union all
select 124  ,  'A4'     ,   'N'    ,  104 union all
select 124  ,  'A5'     ,   'N'    ,  105 union all
select 124  ,  'A6'     ,   'N'    ,  106 union all
select 125  ,  'A7'     ,   'Y'    ,  107 union all
select 125  ,  'A8'     ,   'Y'    ,  108 union all
select 125  ,  'A9'     ,   'N'    ,  109 union all
select 125  ,  'A10'    ,   'N'    ,  110)

select Type, SubType, Flag, Value
from (
  select
    t.*,
    case when min(flag) over (partition by type)  = max(flag) over (partition by type) then 1 else 2 end cnt,
    row_number() over (partition by type, flag order by convert(int, substring(subtype,2,len(subtype)))) rn
  from your_table t
) t where 
(cnt = 1 and (rn = 1 or flag = 'N'))
or (cnt <> 1 and rn = 1);

Produces:

This won't work because `Use of DISTINCT is not allowed with the OVER clause`. — Serge, Jan 15 '17 at 04:49
Thanks GurV , tried with removing distinct and using it into partition by like this but it would not return all the type 124 (condition 2)---- select * from ( select t.*, count(flag) over (partition by type, flag) cnt, row_number() over (partition by type, flag order by subtype) rn from your_table t ) t where (cnt = 1 and (rn = 1 or flag = 'N')) or (cnt <> 1 and rn = 1); — rai01, Jan 15 '17 at 04:54

score 1 · Answer 2 · answered Jan 15 '17 at 04:55

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

I think we can use `Value` for ordering the records instead of `sub-type`. Good solution +1 — Pரதீப், Jan 15 '17 at 05:09
Thanks Serge , please consider that column subtype can have alpha or alphanumeric values.....so extracting the digits would not be possible — rai01, Jan 15 '17 at 05:53
I could apply min after generating row number based on subtype but that doesnt seem to be a good solution overall — rai01, Jan 15 '17 at 05:58

Pரதீப் · Answer 3 · 2017-01-15T05:05:45.433

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

Thanks Prdp , ordering needs to be done with subtype only considering that it can have both alpha and alphanumeric types. — rai01, Jan 15 '17 at 05:54
@rai01 - So how will you find the first record is it just alpha numeric ordering or you have any logic for ordering ? — Pரதீப், Jan 15 '17 at 06:47

Filter rows based on certain conditions

3 Answers3