Help finding IDs with FIVE CONSECUTIVE YEARS of Service 1

[SirWaldizmo]

Hello,
I have a table of two columns one is ID and the second is Year_Of_Service. IDs are duplicated in this table where there are more than one year of service.

Does anyone know of a logic in sql that can find all the IDs that have at least 5 consecutive years of Service.

Any suggestions will be appreciated.
SirWaldizmo

 

[jimbopalmer]

if we have
Jim 1991
Larry 1991
Jim 1992
Jim 1993
Jim 1994
Larry 1994
Jim 1995
Jim 1996

as records


then
select distinct a.ID
from mytable a, mytable b, mytable c, mytable d, mytable e
where b.Year_of_Service = a.Year_of_Service + 1
and c.Year_of_Service = a.Year_of_Service + 2
and d.Year_of_Service = a.Year_of_Service + 3
and e.Year_of_Service = a.Year_of_Service + 4;

I tried to remain child-like, all I acheived was childish.

 

[jimbopalmer]

oops

and a.ID = b.ID
and a.ID = c.ID
and a.ID = d.ID
and a.ID = e.ID;

sorry I tried to remain child-like, all I acheived was childish.

 

[Guest_imported]

Thanks! Jimbopalmer
You're a lifesaver!!!