Table of Contents for

MySQL in a Nutshell, 2nd Edition

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