CODEWITHSUNDEEP
mysqlsqlsql-serverdistance
Rob S
Rob S

Reputation: 3

Entries a specific distance away from others

My table has an NAME and DISTANCE column. I'd like to figure out a way to list all the names that are within N units or less from the same name. i.e. Given: 

NAME    DISTANCE
a   2
a   4
a   3
a   7
a   1
b   3
b   1
b   2
b   5

(let's say N = 2) I would like

a 2 
a 4 
a 3
a 1
...
...

Instead of a 2 a 2 (because it double counts)

I'm trying to apply this method in order to solve for a customerID with claim dates (stored as number) that appear in clusters around each other. I'd like to be able to label the customerID and the claim date that is within say 10 days of another claim by that same customer. i.e., |a.claimdate - b.claimdate| <= 10. When I use this method

WHERE a.CUSTID = b.CUSTID
AND a.CLDATE BETWEEN (b.CLDATE - 10 AND b.CLDATE + 10)
AND a.CLAIMID <> b.CLAIMID

I double count. CLAIMID is unique.

Upvotes: 0

Views: 78

Answers (2)

sgeddes
sgeddes

Reputation: 62831

Since you don't need the text, and just want the values, you can accomplish that using DISTINCT:

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

SQL Fiddle Demo

Given your edits, if you're looking for results between a certain distance, you can use >= and <= (or BETWEEN):

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

You need to add the final criteria of t.distance <> t2.distance so you don't return the entire dataset -- technically every distance is between itself. This would be better if you had a primary key to add to the join, but if you don't, you could utilize ROW_NUMBER() as well to achieve the same results. 

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

Updated SQL Fiddle

Upvotes: 2

dang
dang

Reputation: 193

I like @sgeddes' solution, but you can also get rid of the distinct and or in the join condition like this:

select * from table a
where exists (
    select 1 from table b
    where b.name = a.name
        and b.distance between a.distance - 1 and a.distance + 1
)

This also ensures that rows with equal distance get included and considers a whole range, not just the rows that have a distance difference of exactly n, as suggested by @HABO.

Upvotes: 1

Related Questions