SQL: group by number with error margin

Question

I have a table of clients with their birth year:

• Koen Peeters, 1987
• Sara Stas, 1989
• Karel Tosijn, 1986
• Koen Peeters, 1988
• Koen Peeters, 1950

I want to create a new table grouping the clients by name, and by year.

But the problem is that there is a -1,+1 error margin on the year of birth. So Koen Peeters born in 1987 is the same as Koen Peeters in 1988, but the Koen Peeters born in 1950 is a different Koen Peeters.

Is it possible to do this in SQL? It is kind of a fuzzy group by?

Answer 1

SQL>create table t (n varchar(30), y int);
SQL>insert into t values ('Koen Peeters',1987);
SQL>insert into t values ('Sara Stas',1989);
SQL>insert into t values ('Karel Tosijn',1986);
SQL>insert into t values ('Koen Peeters',1988);
SQL>insert into t values ('Koen Peeters',1950);
SQL>select t1.*
SQL&from t t1
SQL&where not exists (select 1 from t t2
SQL&                  where t1.n = t2.n
SQL&                    and t1.y = t2.y + 1);
n                                        y
============================== ===========
Koen Peeters                          1987
Sara Stas                             1989
Karel Tosijn                          1986
Koen Peeters                          1950

                  4 rows found

Simply pick a clients when there are no one with same name but 1 year older. (Will only pick the older Koen Peeters from 1987/1988.)

Do SELECT DISTINCT if needed.