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:

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:

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:

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.