CODEWITHSUNDEEP
mysqlsql
user3314446
user3314446

Reputation: 11

Finding each rows closest value within the same table

Suppose that I start off with 'Table a'

+-----------+
|iD | Value |
|-----------|
| 0 |  1    |
| 1 |  5    |
| 2 |  6    |
| 3 |  8    |
+-----------+

I then want to create an SQL query that will show the iD, the Value, the Value numerically closest to the current value & the difference between these values e.g

+-----------+------------+------------+
|iD | Value | closestVal | Difference |
|-----------|------------|------------+
| 0 |  1    |    5       |   4        |
| 1 |  5    |    6       |   1        |
| 2 |  6    |    5       |   1        |
| 3 |  8    |    6       |   2        |
+-----------+------------+------------+

From researching I think it may include using something along the lines of ABS(a.Value - b.Value) assuming negative numbers are a possibility although I'm not sure exactly how the referencing of the current row and the comparative rows are handled as well as the output of one value and not a table of values for each comparison.

How would I go about doing this? Any help would be appreciated

Upvotes: 1

Views: 353

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think the easiest way to approach this is with a correlated subquery:

select t.*, abs(closestvalue - value) as difference
from (select t.*,
             (select t2.value
              from tablea t2
              order by abs(t2.value - t.value) asc
              limit 1
             ) as closestvalue
      from tablea t
     ) t;

You can also do this an explicit join, but the logic is a little strange:

select a.id, a.value,
       (case when min(case when a.value > a1.value then a.value - a1.value end) =  min(abs(a.value - a1.value))
             then a.value - min(abs(a.value - a1.value))
             else a.value + min(abs(a.value - a1.value))
        end) as closestvalue
       min(abs(a.value - a1.value)) as difference
from tablea a join
     tablea a1
     on a.id <> a1.id
group by a.id;

Using this approach, the difference is easy to find, by using min(abs()). The problem is then figuring out whether the closest value is bigger or less than the value. The logic here figures it out using conditional aggregation and comparing those results to the actual minimum.

Upvotes: 2

Related Questions