INTERVAL( )

This MySQL function returns the position where search_value would be located in a comma-separated list of ordered_value arguments.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

INTERVAL(search_value, ordered_value,  . . . )

Explanation

This function returns the position where search_value would be located in a comma-separated list of ordered_value arguments. In other words, the function returns the first ordered_value that is less than or equal to search_value. All arguments are treated as integers, and the caller must list the ordered_value arguments in increasing order. If search_value would be located before the first ordered value, 0 is returned. If search_value would be located after the last ordered value, the position of that value is returned.

Examples

For example, suppose that a professor at our fictitious college has given the same few exams every semester for the last four semesters. Suppose that he has a table containing a row for each semester, containing a column for each exam that contains the average of student grades for the semester. Now the professor wants to know how the average score for the same exam for the current semester compares against the previous semesters: he wants to know how the students on average rank by comparison. We could find this answer by running the following SQL statement:

SELECT INTERVAL(
   (SELECT AVG(exam1) FROM student_exams),
   S1,S2,S3,S4) AS Ranking
FROM
  (SELECT
    (SELECT exam1_avg FROM student_exams_past
       ORDER BY exam1_avg LIMIT 0,1) AS S1,
    (SELECT exam1_avg FROM student_exams_past
       ORDER BY exam1_avg LIMIT 1,1) AS S2,
    (SELECT exam1_avg FROM student_exams_past
       ORDER BY exam1_avg LIMIT 2,1) AS S3,
    (SELECT exam1_avg FROM student_exams_past
       ORDER BY exam1_avg LIMIT 3,1) AS S4) AS exam1_stats;

In this complex example, we're running four subqueries to get the average exam score stored (S1, S2, S3, and S4) in the same column for the four semesters for which we have data. Then we're putting each of these values into one row of a derived table (exam1_stats). We will then select each column of that limited derived table for the strings to compare against in the INTERVAL() function. For the first parameter of that function, though, we're running yet another subquery to determine the average grades of students for the same exam for the current semester. The results will be a number from 0 to 4, depending on how this semester's average compares.