Going Beyond the Typical Sort: Sorting by Specific Values with MySQL’s Order By Clause

Going Beyond the Typical Sort: Sorting by Specific Values with MySQL’s Order By Clause

When performing MySQL database queries, have there been times where the "Order By" clause doesn't seem to cut it? Well it turns out that we can do more than sort by one or more columns in ascending and descending order. MySQL's FIELD() function provides a way to target a specific value from within a column. Let's take a closer look at the function.

The Example

Most of us have likely used the "Order By" clause before. For example, if we're displaying a list of staff members, our code might look like:

//GET THE STAFF MEMBERS
$sql = "SELECT first_name, last_name, job_title FROM staff_directory ORDER BY last_name, first_name";
$result = mysql_query($sql);

//DISPLAY THE STAFF MEMBERS
print '<table cellpadding="5" cellspacing="1" border="1">';
print '<tr><th scope="col">First Name</th><th scope="col">Last Name</th><th scope="col">Job Title</th></tr>';
while($row = mysql_fetch_array($result)) {
     print "<tr><td>{$row['first_name']}</td><td>{$row['last_name']}</td><td>{$row['job_title']}</td></tr>";
}
print '</table>';

The query sorts the staff members by last name and then by first. Once the code runs, we end with a table like the following:

First Name	Last Name	Job Title
Jake	Bible	Research Fellow
Jill	Jones	Research Fellow
George	Lucas	Director
Jim	Parsons	Theoretical Physicist
John	Smith	Assistant

However, what if we wanted something else? If the most viewed bio is the one for the organization's director, we might want their bio to appear near the top. The FIELD()function could be utilized so the director appears first and then the normal sort order takes over.

//GET THE STAFF MEMBERS
$sql = "SELECT first_name, last_name, job_title FROM staff_directory ORDER BY FIELD(job_title, 'Director') DESC, last_name, first_name";
$result = mysql_query($sql);
//...

Our staff table now looks like the following:

First Name	Last Name	Job Title
George	Lucas	Director
Jake	Bible	Research Fellow
Jill	Jones	Research Fellow
Jim	Parsons	Theoretical Physicist
John	Smith	Assistant

The Catch

Although the FIELD() function provides a nice way to target specific values, there is something to be aware of. As mentioned in the article (Ordering by specific field values with MySQL) which taught me about the function, "values that are in the column that are not in the FIELD() function will appear in a more or less random order before the specified values." The previous example isn't affected by the glitch. But if we were only sorting by job title:

//GET THE STAFF MEMBERS
$sql = "SELECT first_name, last_name, job_title FROM staff_directory ORDER BY FIELD(job_title, 'Director') DESC";
$result = mysql_query($sql);
//...

The table would look like the following:

First Name	Last Name	Job Title
George	Lucas	Director
Jim	Parsons	Theoretical Physicist
John	Smith	Assistant
Jake	Bible	Research Fellow
Jill	Jones	Research Fellow

To correct this behavior, we just need to repeat the column to sort by.

//GET THE STAFF MEMBERS
$sql = "SELECT first_name, last_name, job_title FROM staff_directory ORDER BY FIELD(job_title, 'Director') DESC, job_title";
$result = mysql_query($sql);
//...

Conclusion

So the next time you have special sorting needs, don't instantly run to PHP, multiple queries, or adding extra fields to the MySQL database. The FIELD() function may be the answer.