CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
rai01
rai01

Reputation: 31

Filter rows based on certain conditions

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.

Upvotes: 3

Views: 294

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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:

enter image description here

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 1

Serge
Serge

Reputation: 4036

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

Upvotes: 1

Related Questions