mysql - Entries a specific distance away from others -

my table has name , distance column. i'd figure out way list names within n units or less same name. i.e. given:

name    distance   2   4   3   7   1 b   3 b   1 b   2 b   5 

(let's n = 2) like

a 2  4  3 1 ... ... 

instead of 2 2 (because double counts)

i'm trying apply method in order solve customerid claim dates (stored number) appear in clusters around each other. i'd able label customerid , claim date within 10 days of claim same customer. i.e., |a.claimdate - b.claimdate| <= 10. when use method

where a.custid = b.custid , a.cldate between (b.cldate - 10 , b.cldate + 10) , a.claimid <> b.claimid 

i double count. claimid unique.

since don't need text, , want values, can accomplish using distinct:

select distinct t.name, t.distance yourtable t   join yourtable t2 on t.name = t2.name      , (t.distance = t2.distance+1 or t.distance = t2.distance-1) order t.name 

sql fiddle demo

given edits, if you're looking results between distance, can use >= , <= (or between):

select distinct t.name, t.distance yourtable t   join yourtable t2 on t.name = t2.name      , t.distance >= t2.distance-1      , t.distance <= t2.distance+1     , t.distance <> t2.distance order t.name 

you need add final criteria of t.distance <> t2.distance don't return entire dataset -- technically every distance between itself. better if had primary key add join, if don't, utilize row_number() achieve same results.

with cte (   select name, distance, row_number() on (partition name order (select null)) rn   yourtable   ) select distinct t.name, t.distance cte t   join cte t2 on t.name = t2.name      , t.distance >= t2.distance-1     , t.distance <= t2.distance+1     , t.rn <> t2.rn order t.name 

updated sql fiddle