CODEWITHSUNDEEP
mysql
RotemY
RotemY

Reputation: 35

find the closest value - MySQL

How can I find the closest value for a multiple values?

for example: I have this table

A 1

B 2

C 3

D 7

E 11

F 12

so - 

for A i want to get B, 
for B - A and C,
for C - B,
for D - E,
for E - F,
for F - E,

Thanks

Upvotes: 1

Views: 74

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can use a little translation table like this

CREATE TABLE `translate` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `from` char(1) DEFAULT NULL,
  `to` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `from` (`from`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `translate` (`id`, `from`, `to`)
VALUES
    (1, 'A', 'B'),
    (2, 'B', 'A'),
    (3, 'B', 'C'),
    (4, 'C', 'B'),
    (5, 'D', 'E'),
    (6, 'E', 'F'),
    (7, 'F', 'E');

Sample

    MariaDB []> SELECT * FROM yourTable;
+----+------+------+
| id | c    | val  |
+----+------+------+
|  1 | A    |    1 |
|  2 | B    |    2 |
|  3 | C    |    3 |
|  4 | D    |    7 |
|  5 | E    |   11 |
|  6 | F    |   12 |
+----+------+------+
6 rows in set (0.00 sec)

MariaDB []>
MariaDB []> SELECT y.*
    -> FROM yourTable y
    -> LEFT JOIN translate t ON t.to = y.c
    -> WHERE t.from = 'C';
+----+------+------+
| id | c    | val  |
+----+------+------+
|  2 | B    |    2 |
+----+------+------+
1 row in set (0.00 sec)

MariaDB []> SELECT y.*
    -> FROM yourTable y
    -> LEFT JOIN translate t ON t.to = y.c
    -> WHERE t.from = 'B';
+----+------+------+
| id | c    | val  |
+----+------+------+
|  1 | A    |    1 |
|  3 | C    |    3 |
+----+------+------+
2 rows in set (0.00 sec)

MariaDB []>

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Try this:

SELECT t1.col1, t2.col1
FROM mytable AS t1
INNER JOIN mytable AS t2 
  ON t1.col1 <> t2.col1
LEFT JOIN mytable AS t3 
  ON t3.col1 <> t2.col1 AND t3.col1 <> t1.col1 AND 
     ABS(t3.col2 - t1.col2) < ABS(t2.col2 - t1.col2)
WHERE t3.col1 IS NULL  
ORDER BY t1.col1

Demo here

Upvotes: 5

Related Questions