How to Order by Specific Items First Then by Time in MySQL

Details

Description

Discover methods to intelligently organize your MySQL query results by specific IDs followed by creation date.
---
This video is based on the question https://stackoverflow.com/q/64602808/ asked by the user 'Rasik' ( https://stackoverflow.com/u/6212667/ ) and on the answer https://stackoverflow.com/a/64607858/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MySQL: Order by specific items first then by time

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Ordering Rows in MySQL: Specific IDs First Then by Time

When working with databases, one common requirement is to retrieve data in a specific order that suits your business logic or reporting needs. In this guide, we'll tackle a problem in MySQL where you might want to prioritize certain records before sorting the rest based on their creation timestamp. This type of ordering can be useful in various scenarios such as displaying featured items followed by the newest entries.

The Challenge

Given a table that contains user-generated names along with timestamps, suppose you have the following records:

idnamecreated_at1alpha2020-10-23 17:30:352beta2020-10-24 17:30:353gamma2020-10-25 17:30:354kilo2020-10-26 17:30:355charlie2020-10-27 17:30:356hector2020-10-28 17:30:35Your task is to retrieve these records, but you want the records with IDs 6, 3, and 2 to appear first, followed by the rest of the records ordered by their created_at time in descending order. The expected output order is:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

Using FIELD() Function

Although you might think of using the FIELD() function for ordering specific items, it can be tricky in combination with other sorting criteria. This is because FIELD() returns 0 if there are no matches found. To achieve the desired order, you can use the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

FIELD(id, 6, 3, 2): This function attempts to match the ID values with the specified list (6, 3, 2). The function returns the index of each ID if found in the list; otherwise, it returns 0.

NULLIF(..., 0): Converts the 0 result of FIELD() to NULL, which is then evaluated by COALESCE().

COALESCE(..., 999999): If NULL is produced (i.e., for other IDs), this function assigns a default high value 999999 so that those records push down the order.

created_at DESC: Finally, we ensure that any records not in the specified list are ordered based on the creation time in descending order.

Alternative Approach Using CASE Statement

If you intend to have the specific IDs always in descending order, you could also use a CASE statement:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query:

CASE WHEN id IN (6, 3, 2) THEN id END: This will generate a value (the ID) for those specific entries and NULL for all others.

The sorting will first arrange these specific IDs in descending order, followed by all other records sorted by created_at in descending order.

Conclusion

In summary, whether you use the FIELD() function or the CASE statement, MySQL provides flexible options to customize your order of results. This approach allows you to highlight specific items before presenting the rest of your data, enhancing the usability of your database queries. So next time you need to prioritize certain records in MySQL, consider these methods for clearer, more effective data presentation.

If you have further questions or need assistance with complex queries, feel free to reach out! Happy querying!