How to Retrieve a Specific Item First in a Sorted MySQL Query?

Prioritizing a Specific Item in MySQL Query Results

This guide demonstrates how to retrieve all records from a MySQL friends table (containing id and name columns), ensuring a specific friend (e.g., with id 5) appears first in the sorted results. We'll focus on MySQL version 5.1.x.

The most efficient approach uses a conditional ORDER BY clause:

SELECT id, name
FROM friends
ORDER BY CASE WHEN id = 5 THEN 0 ELSE 1 END, id ASC;

This query prioritizes id = 5 by assigning it a value of 0 in the CASE statement. All other IDs receive a value of 1, effectively placing id = 5 at the top. The secondary ORDER BY id ASC then sorts the remaining results in ascending order of ID.

Alternative (Less Efficient) Methods:

While less efficient than the CASE statement method, you could achieve a similar result using UNION ALL:

SELECT id, name
FROM friends
WHERE id = 5
UNION ALL
SELECT id, name
FROM friends
WHERE id != 5
ORDER BY id ASC;

This approach first selects the specific ID, then selects all other IDs, finally sorting the combined result. However, this method is generally slower than the CASE statement approach, especially with larger datasets.

Choose the CASE statement method for optimal performance and readability. It directly addresses the sorting requirement within a single query.

The above is the detailed content of How to Retrieve a Specific Item First in a Sorted MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!