Tomer Tomer - 28 days ago 6x
MySQL Question

MySQL Query with condition based on different rows

I have a table with measurements of stars. Each row (i.e. a measurement) contains the star's ID and its Period.

Lets say, for example:

Star_ID Period
1 3.6
1 3.9
2 4.11
2 4.12
2 8.5
3 7.1
4 1.31
4 1.32

I want to write a query that returns the Star_ID's only of the stars which have measurements with "close" periods: stars which contain 2 periods which satisfies P1-P2 < x . In our example, for x = 0.02 it should return {2,4}.

Can anyone help me with that?

Thank a lot :)


One method is with a self-join and aggregation:

select t.star_id
from stars t join
     stars t2
     on t.star_id = t2.star_id and t.period <> t2.period
group by t.star_id
having min(abs(t.period - t2.period)) < 0.02;

Note: This will ignore two duplicate rows (a difference of 0).