How to find the interval position of a given number in a sorted number list using the INTERVAL() function?

INTERVAL(x, int1, int2, ...) is a MySQL built-in function that returns the interval position of x in a given sorted number list. It uses the following algorithm:

INTERVAL(x, int1, int2, ...intn) = 0, 
  if x < int1

INTERVAL(x, int1, int2, ...intn) = i, 
  if inti <= x < inti+1

INTERVAL(x, int1, int2, ...intn) = n, 
  if intn <= x

For example:

SELECT INTERVAL(22, 23, 30, 44, 200);
  -- +-------------------------------+
  -- | INTERVAL(22, 23, 30, 44, 200) |
  -- +-------------------------------+
  -- |                             0 |
  -- +-------------------------------+

SELECT INTERVAL(10, 1, 10, 100, 1000);
  -- +--------------------------------+
  -- | INTERVAL(10, 1, 10, 100, 1000) |
  -- +--------------------------------+
  -- |                              2 |
  -- +--------------------------------+

SELECT INTERVAL(1000, 1, 10, 100, 1000);
  -- +----------------------------------+
  -- | INTERVAL(1000, 1, 10, 100, 1000) |
  -- +----------------------------------+
  -- |                                4 |
  -- +----------------------------------+

Note that all arguments are treated as numbers (MySQL document incorrectly stated as integers). For example:

SELECT INTERVAL(1000, 1, 10, 100, '1000');
  -- +------------------------------------+
  -- | INTERVAL(1000, 1, 10, 100, '1000') |
  -- +------------------------------------+
  -- |                                  4 |
  -- +------------------------------------+

SELECT INTERVAL(100.6, 1, 10, 100.7, '1000');
  -- +---------------------------------------+
  -- | INTERVAL(100.6, 1, 10, 100.7, '1000') |
  -- +---------------------------------------+
  -- |                                     2 |
  -- +---------------------------------------+

SELECT INTERVAL(100.7, 1, 10, 100.7, '1000');
  -- +---------------------------------------+
  -- | INTERVAL(100.7, 1, 10, 100.7, '1000') |
  -- +---------------------------------------+
  -- |                                     3 |
  -- +---------------------------------------+

Also note that INTERVAL() uses the binary search algorithm. If the value list is not sorted, you may get undesirable result. For example:

SELECT INTERVAL(5.5, 1, 2, 3, 4, 5, 6, 7, 8, 9);
  -- +------------------------------------------+
  -- | INTERVAL(5.5, 1, 2, 3, 4, 5, 6, 7, 8, 9) |
  -- +------------------------------------------+
  -- |                                        5 |
  -- +------------------------------------------+

SELECT INTERVAL(5.5, 1, 9, 2, 8, 3, 7, 4, 6, 5);
  -- +------------------------------------------+
  -- | INTERVAL(5.5, 1, 9, 2, 8, 3, 7, 4, 6, 5) |
  -- +------------------------------------------+
  -- |                                        7 |
  -- +------------------------------------------+

SELECT INTERVAL(5.5, 1, 9, 2, 3, 8, 7, 4, 6, 5);
  -- +------------------------------------------+
  -- | INTERVAL(5.5, 1, 9, 2, 3, 8, 7, 4, 6, 5) |
  -- +------------------------------------------+
  -- |                                        4 |
  -- +------------------------------------------+

Reference information of the INTERVAL() function:

INTERVAL(x, int1, int2, ...): pos
  Returns the interval position of x in a sorted number list.

Arguments, return value and availability:
  x: Required. The number to be compared.
  int1, int2, ...: Two or more numbers to be compared against.
  pos: Return value. The interval position where x should be located.
  Available since MySQL 4.0.

⇒ LN() - Natural Logarithm

⇐ EXPORT_SET() - Exporting Binary Set to On/Off Flags

⇑ MySQL Functions on Numeric Values

⇑⇑ MySQL Function References