MySQL in a Nutshell, 2nd Edition

Name

INTERVAL()

Synopsis

INTERVAL(search_value, ordered_value, ...)

This function returns the position in which 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.

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, and 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 ...