October 5, 2021 at 11:50 pm
Hi,
I have a table that has a duplicate rows based on Id column. If an Id has multiple entries, I want to pick a row that has its branch value as 'head quarters' as first priority, else branch with 'regional' value should be selected.
For the below sample data, output should be like this:
1, 'head quarters', 'Los Angeles'
2, 'regional', 'Seattle'
What is the easy way to query this ?
create table #temp (
Id int not null,
Branch varchar(50),
City varchar(50)
) ;
insert into #temp (id, branch, city)
values (1, 'head quarters', 'Los Angeles'), (2, 'regional', 'Seattle'), (1, 'regional', 'Dallas')
Thanks,
Naveen.
Every thought is a cause and every condition an effect
October 6, 2021 at 12:05 am
;with cte as
(
select distinct t.Id
from #temp t
)
select t.*
from cte x
cross apply(select top(1) *
from #temp t
where t.Id = x.Id
order by case t.Branch when 'head quarters' then 0 else 1 end) t
October 6, 2021 at 12:57 am
;WITH cte_add_row_nums AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Branch) AS row_num
FROM #temp
)
SELECT id, branch, city
FROM cte_add_row_nums
WHERE row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 6, 2021 at 6:36 am
thank you
Thanks,
Naveen.
Every thought is a cause and every condition an effect
October 7, 2021 at 11:00 am
Another method
select *
from #temp t
where t.Branch = 'head quarters'
union all
select *
from #temp t
where not exists(select *
from #temp t2
where t2.Id = t.Id
and t2.Branch = 'head quarters')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply