i have table containing name, relative name , age follows
sno name relative name age
1 aakash sood rajeev sood 22
2 aakash sood rajeev sood 21
3 aamita kumari rajeev verma 35
4 aamita kumari rajeev verma 46
5 aakash sood rajeev sood 23
6 naresh verma shiv lal sahu 37
7 naresh verma shiv lal sahu 38
now want know if possible in sql server 2005 database extract duplicate records name, relative name , ages in +1 or -1 range. means extract these records sno name relative name age
1 aakash sood rajeev sood 22
2 aakash sood rajeev sood 21
5 aakash sood rajeev sood 23
6 naresh verma shiv lal sahu 37
7 naresh verma shiv lal sahu 38
, suggections in advance
check out:
--create table sno (sno varchar(50), name varchar(100), rel_name varchar(100), age int) --insert sno values --('1','aakash sood','rajeev sood','22'), --('2','aakash sood','rajeev sood','21'), --('3','aamita kumari','rajeev verma','35'), --('4','aamita kumari','rajeev verma','46'), --('5','aakash sood','rajeev sood','23'), --('6','naresh verma','shiv lal sahu','37'), --('7','naresh verma','shiv lal sahu','38') select distinct n1.* sno n1 join sno n2 on n1.name = n2.name , n1.rel_name = n2.rel_name , n1.sno != n2.sno , (n1.age = n2.age or n1.age = n2.age + 1 or n1.age = n2.age - 1)
Comments
Post a Comment