Wednesday 8 March 2017

Select rows with same id but different value in another column

create table #temp(ID int,Name varchar(10))
insert into #temp values(1,'Masum')
insert into #temp values(1,'Reza')
insert into #temp values(2,'Masum')
insert into #temp values(2,'Masum')
insert into #temp values(3,'Masum')
insert into #temp values(3,'Reza')

SELECT ID,Name FROM #temp WHERE ID IN
 (
SELECT a.ID FROM #temp a
JOIN #temp b on b.ID = a.ID AND b.Name <> a.Name
)order by ID

Now i want to select Distinct ID from the duplicates ID. In this case CTE is more usefull to fetch the records.

with cte
as
(
SELECT ID,Name FROM #temp WHERE ID IN (
    SELECT a.ID FROM #temp a
    JOIN #temp b on b.ID = a.ID AND b.Name <> a.Name
)
)
select distinct ID from cte

No comments:

Post a Comment