Collections:
INTERVAL() - Interval Position of Sorted List
How to find the interval position of a given number in a sorted number list using the INTERVAL() function?
✍: FYIcenter.com
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.
⇐ EXPORT_SET() - Exporting Binary Set to On/Off Flags
2023-12-19, 273🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...