CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

Where clause by grouped ID's

I have following table:

ID | source | Name | Age | ... | ...
1  | SQL    | John | 18  | ... | ...
2  | SAP    | Mike | 21  | ... | ...
2  | SQL    | Mike | 20  | ... | ...
3  | SAP    | Jill | 25  | ... | ...
4  | SAP    | Jake | 31  | ... | ...
4  | SAP    | Jake | 30  | ... | ...

I want to have one record for each ID. The idea behind this is that if the ID comes only once (no matter the Source), that record will be taken. But, If there are 2 records for one ID, the one containing SQL as source will be the used record here.

However, if there are 2 of the same ID, which both have SAP as source, both need to be ignored.

So, In this case, the result will be:

ID | source | Name | Age | ... | ...
1  | SQL    | John | 18  | ... | ...
2  | SQL    | Mike | 20  | ... | ...
3  | SAP    | Jill | 25  | ... | ...

This question is similar to the one I asked yesterday, but I am stuck with the part where I need to ignore dubbels from SAP.

Any suggestions/ideas?

Upvotes: 1

Views: 66

Answers (6)

Stevie Gray
Stevie Gray

Reputation: 113

Working through the fine examples, please find 2 ways to complete this.

One using a 'Having Count' and the other using OVER (partition by ID).

declare @t table (ID int,source varchar(31),Name varchar(17),Age int)
insert into @t(ID,source,Name,Age) values
(1,'SQL','John',18),
(2,'SAP','Mike',21),
(2,'SQL','Mike',20),
(3,'SAP','Jill',25),
(4,'SAP','Jake',31),
(4,'SAP','Jake',30)


SELECT * 
FROM @t 
WHERE ID in (
       SELECT ID FROM @t
       group by ID
having count(ID) = 1)
OR source = 'SQL'

-- alternatively 

select id, source, name, age
from
(
  select id, source, name, age, count(*) over (partition by id) as cnt
  from @t
) counted
where cnt = 1 or source = 'SQL';

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @YourTable table (ID int,source varchar(25),Name varchar(25), Age int)
Insert Into @YourTable values
(1  ,'SQL', 'John', 18  ),
(2  ,'SAP', 'Mike', 21  ),
(2  ,'SQL', 'Mike', 20  ),
(3  ,'SAP', 'Jill', 25  ),
(4  ,'SAP', 'Jake', 31  ),
(4  ,'SAP', 'Jake', 30  )

Select ID,Source,Name,Age
 From  (
        Select *
              ,RN  = Row_Number() over (Partition By ID Order By Case When Source = 'SQL' Then 0 Else 1 End)
              ,Cnt = sum(1) over (Partition By ID,Source)
          From  @YourTable
       ) A
 Where RN=1 and Cnt=1

Returns

ID  Source  Name    Age
1   SQL     John    18
2   SQL     Mike    20
3   SAP     Jill    25

Upvotes: 1

Chanukya
Chanukya

Reputation: 5893

SELECT id,source,name, age
FROM #f 
WHERE ID in (
   SELECT ID FROM #f
   group by ID
   having count(ID) = 1)
OR source = 'SQL'

output

ID  source  Name    Age
1   SQL John    18
2   SQL Mike    20
3   SAP Jill    25

Upvotes: 1

DVT
DVT

Reputation: 3127

WITH cte AS (
    SELECT ID, source, Name, Age
        , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE source WHEN 'SAP' THEN -1 ELSE 1 END CASE) as RN
    FROM
        Table
)
SELECT ID, source, Name, Age
FROM cte
WHERE source <> 'SAP' AND rn = 1;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

Count records per ID and take those records for which the count is 1 or the source is SQL.

select id, source, name, age
from
(
  select id, source, name, age, count() over (partition by id) as cnt
  from mytable
) counted
where cnt = 1 or source = 'SQL';

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

We can count the number of rows for each ID and then use that to apply further logic:

declare @t table (ID int,source varchar(31),Name varchar(17),Age int)
insert into @t(ID,source,Name,Age) values
(1,'SQL','John',18),
(2,'SAP','Mike',21),
(2,'SQL','Mike',20),
(3,'SAP','Jill',25),
(4,'SAP','Jake',31),
(4,'SAP','Jake',30)

;With Counted as (
    select
        *,
        COUNT(*) OVER (PARTITION BY ID) as cnt
    from
        @t
)
select
    *
from
    Counted
where
    cnt = 1 or
    (cnt = 2 and source = 'SQL')

Results:

ID          source                          Name              Age         cnt
----------- ------------------------------- ----------------- ----------- -----------
1           SQL                             John              18          1
2           SQL                             Mike              20          2
3           SAP                             Jill              25          1

You should be able to adapt this approach and the ones shown in answers to your previous question to hone your search criteria in each way that makes sense.

Upvotes: 1

Related Questions