CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

Where clause if there are multiple of the same ID

I have following table:

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

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.

So, In this case, the result will be:

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

I did this with a partition over (ordered by Source desc), but that wouldn't work well if a third source will be added one day.

Any other options/ideas?

Upvotes: 3

Views: 309

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

You can use the WITH TIES clause and the window function Row_Number()

Select Top 1 With Ties *
  From  YourTable
  Order By Row_Number() over (Partition By ID Order By Case When Source = 'SQL' Then 0 Else 1 End)

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460168

The easiest approach(in my opinion) is using a CTE with a ranking function:

with cte as
(
   select ID, source, Name, Age, ... , 
          rn = row_number() over (partition by ID order by case when source = 'sql'
                                                           then 0 else 1 end asc)
   from dbo.tablename
)
select ID, source, Name, Age, ...
from cte
where rn = 1

Upvotes: 4

Lamak
Lamak

Reputation: 70658

You can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY ID
                                    ORDER BY CASE WHEN [Source] = 'SQL' THEN 1 ELSE 2 END)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE RN = 1;

Upvotes: 4

Vítek
Vítek

Reputation: 138

How about 

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

Upvotes: 3

Related Questions