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