If you're new here, you may want to subscribe to my Newsletter. Thanks for visiting!

You can use this tutorial in various projects where the requirement is to sequence the records based on the order that has already been predefined.

Imagine having to write code where you are supposed to display articles on the basis of an order predefined by the administrator of the site. I had a similar challenge with one of my sites www.careercurry.com where I had to show article listings on the home page based on the sequencing that I set through the admin control panel.

I had set the article sequencing as 1, 3, 7, 2, 9, 10, 4 and the most obvious query was as follows:

   SELECT title, description 
   FROM articles WHERE id IN (1, 3, 7, 2, 9, 10, 4)

The above query would have been very simple… but the output was a little different than what I had expected. The result query set was ordered as 1, 2, 3, 4, 7, 9, 10.

The reason why this happened was because the query was on primary key and because there was no ORDER BY instruction for the query, it sorted the results based on the primary key index.

The challenge was to seek the result in the exact same order, without having to write additional logic on my application layer. The solution that I researched and discovered was as follows:

   SELECT id, title, description 
   FROM articles WHERE id IN (1, 3, 7, 2, 9, 10, 4) 
   ORDER BY FIELD(id, 1, 3, 7, 2, 9, 10, 4)

The above gave me the result as expected. I then wanted to dig further to know what happened inside the FIELD() function. The FIELD() function returns the record position of the field i.e. ‘id’ based on the data i.e 1, 3, 7, 2, 9, 10, 4.

Therefore, it returned the record position for each and every data 1, 3, … 4. So the ORDER BY syntax ordered the result based on the record positions rather than the natural order of the ‘id’ PRIMARY KEY index.

You can read more on FIELD() function here FIELD()

Please leave behind any comments that you might have - a question, more information or doubts.