how to get duplicate row without PK ?create table t1 (name varchar(20), age int) -- no PKinsert into t1 values('john', 20);insert into t1 values('john', 20);insert into t1 values('peter', 20);'john' is duplicate in the table. how to get this out ? I have coded a version, it works, want to know if we have a concise method.select ROW_NUMBER() over(order by name) as row, name, age into #tmp1from t1select * from #tmp1select ROW_NUMBER() over(order by name) as row, name, age into #tmp2from t1select * from #tmp2select distinct t1.name, t1.age from #tmp1 t1 cross join #tmp2 t2where t1.row != t2.rowand t1.name = t2.nameand t1.age = t2.age
how to get duplicate row without PK ?
Posted - 2011-12-16 : 15:57:03
