Reputation: 39
I have a table containing Name, Relative name and age as 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 I want to know if this is possible in SQL server 2005 database that we extract duplicate records whose name, relative name and ages are in +1 or -1 range. I means to extract these records only
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
Thanks for help and suggections in advance
Upvotes: 1
Views: 60
Reputation: 7989
Check this out:
--create table sno (sno varchar(50), name varchar(100), rel_name varchar(100), age int)
--insert into 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.*
from sno n1
join sno n2 on n1.name = n2.name
and n1.rel_name = n2.rel_name
and n1.sno != n2.sno
and (n1.age = n2.age or n1.age = n2.age + 1 or n1.age = n2.age - 1)
Upvotes: 1