sql server - Extract duplicate records depending upon age range -


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) 

sqlfiddle


Comments