CODEWITHSUNDEEP
sql-serverduplicatesrecords
user837414
user837414

Reputation: 39

Extract duplicate records depending upon age range

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

Answers (1)

Andrey Morozov
Andrey Morozov

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)

SQLFiddle

Upvotes: 1

Related Questions