CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Niladri Biswas
Niladri Biswas

Reputation: 4171

How to do grouping of sets in Sql Server and Filter records

I have a table that looks as under

SALES_ORDER_ID  DATEDIFFERENCE  FLAG
1               -40             1
1               -20             1
2                40             0
2               -10             1
3                12             0
4               -70             1
5                60             0
5                23             0

//ddl

Declare @t table (sales_order_id int,DateDifference int, Flag int)
Insert Into @t 
                Select 1,-40,1 Union All Select 1,-20,1 Union All
                Select 2,40,0 Union All Select 2,-10,1 Union All
                Select 3,12,0 Union All Select 4,-70,1 Union All
                Select 5,60,0 Union All Select 5,23,0
Select *
From @t

The output should be 

sales_order_id  DateDifference  Flag
3                      12          0
5                      60          0
5                      23          0

i.e. the group (Sales_Order_Id) where all the items are having the Flag as 0.

I mean Sales_Order_Id = 2 will not appear since at least one item is not zero (2,-10,1)

Please do ask me if the information is not sufficient.

How to do this query?

Edit

I found my answer.. though still looking for a more elegant one

 ;with cte as(
    Select 
        sales_order_id = Case when x.sales_order_id is null then y.sales_order_id else x.sales_order_id end 
        ,x.CountFor0
        ,y.CountFor1
    From 
            (Select sales_order_id,CountFor0 = count(*)
            from @t 
            where Flag = 0
            group by sales_order_id )x
    Full Join 

            (Select sales_order_id,CountFor1 =count(*)
            from @t 
            where Flag = 1
            group by sales_order_id )y
    On x.sales_order_id = y.sales_order_id)

    Select *
    From cte
    where CountFor1 is null

Thanks to all

Since I don't have much reputation , so cannot post my answer in answer section

Upvotes: 3

Views: 150

Answers (6)

lc.
lc.

Reputation: 116498

Ok, now I understand what you are trying to do. You can use NOT EXISTS, as others have suggested (sqlfiddle):

SELECT *
FROM @t T1
WHERE Flag = 0
AND NOT EXISTS (
    SELECT * 
    FROM @t T2 
    WHERE T1.sales_order_id = T2.sales_order_id
    AND T2.Flag <> 0)

You can also use NOT IN, as others have also suggested (sqlfiddle):

SELECT *
FROM @t T1
WHERE Flag = 0
AND sales_order_id NOT IN (
    SELECT sales_order_id 
    FROM @t T2 
    WHERE T2.Flag <> 0)

And you can even use a JOIN and check the joined table has no row (id is NULL) (sqlfiddle):

SELECT *
FROM @t T1
LEFT OUTER JOIN @t T2 ON T1.sales_order_id = T2.sales_order_id AND T2.Flag <> 0
WHERE T1.Flag = 0
AND T2.sales_order_id IS NULL

Original answer:

You need a WHERE clause to filter the results:

SELECT * 
FROM @t
WHERE Flag = 0

Check out the sqlfiddle here

I am assuming you also want the (2,40,0) row in the results, though.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I just wanted to add that this is a great place to use windows functions:

select sales_order_id, DateDifference, flag
from (select t.*, SUM(flag) over (partition by sales_order_id) as sumflag
      from @t t
     ) t
where sumflag = 0

Upvotes: 1

Tirumudi
Tirumudi

Reputation: 443

Try this.. 

SELECT sales_order_id,DateDifference,Flag FROM @t WHERE sales_order_id NOT IN 
 (SELECT sales_order_id FROM @t WHERE Flag=1)

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Select * From @t 
where Flag=0 and sales_order_id not in 
(select sales_order_id from @t  where Flag !=0)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select *
from @T as T1
where not exists(select *
                 from @T as T2
                 where T1.sales_order_id = T2.sales_order_id and
                       T2.Flag = 1)

Upvotes: 0

difficult_to_cure
difficult_to_cure

Reputation: 26

Good day!

I think that will help:

Select * from @t WHERE FLAG=0;

Upvotes: 1

Related Questions