Using UNION and ORDER BY in MySQL: A Solution for Group-wise Sorting

Problem Background and Requirements Analysis

In web development, it is often necessary to retrieve different types of data from a database and merge and sort them based on specific criteria. For example, in a geolocation search application, users may want to display exact match results, results within 5 km, and results within 5-15 km simultaneously. While MySQL's UNION operator easily combines results from multiple SELECT queries, adding an ORDER BY clause directly after UNION sorts all results globally, failing to maintain independent ordering within each subquery's results.

Core Solution: Application of Pseudo-Column (Rank)

To address this issue, we introduce a pseudo-column (commonly named Rank) that assigns a unique priority value to each subquery. By sorting first by Rank and then by other fields (e.g., ID or add_date), group-wise sorting can be achieved. Below is a complete example based on the user scenario:

SELECT *
FROM (
    SELECT 1 AS Rank, id, add_date, name, distance
    FROM locations
    WHERE name = '<search_place>'
    UNION ALL
    SELECT 2 AS Rank, id, add_date, name, distance
    FROM locations
    WHERE distance < 5
    UNION ALL
    SELECT 3 AS Rank, id, add_date, name, distance
    FROM locations
    WHERE distance BETWEEN 5 AND 15
) AS combined_results
ORDER BY Rank, add_date DESC;

In this query:

• The first subquery (Rank=1) retrieves records that exactly match the search place.
• The second subquery (Rank=2) retrieves records within 5 km distance.
• The third subquery (Rank=3) retrieves records with distance between 5 and 15 km.
• The outer query first sorts by Rank to ensure each group of results is displayed together; then it sorts by add_date in descending order to arrange records within each group chronologically.

Technical Details and Optimization Suggestions

Using UNION ALL instead of UNION can improve query performance, as UNION ALL does not remove duplicate rows, avoiding additional deduplication overhead. In most search scenarios, the result sets of subqueries are mutually exclusive, making UNION ALL safe and efficient.

If more complex sorting logic is needed for each subquery, ORDER BY can be used inside the subqueries, but note: directly using ORDER BY in UNION subqueries may not yield expected results unless combined with LIMIT. Therefore, it is recommended to handle sorting uniformly in the outer query.

Common Issues and Error Handling

In practice, developers might encounter the following issues:

• Inconsistent Data Types: Ensure all subqueries have the same number of columns and compatible data types; otherwise, the UNION operation will fail.
• Performance Bottlenecks: For large datasets, consider adding indexes to the distance field to speed up range queries.
• Pagination Handling: When implementing pagination with LIMIT and OFFSET, ensure sorting logic aligns with pagination to avoid duplicate or missing data.

Conclusion

By introducing a pseudo-column Rank, we successfully resolve the group-wise sorting issue when combining UNION and ORDER BY in MySQL. This method is not only applicable to geolocation searches but can be extended to any scenario requiring merged result sets with maintained individual sort orders. Developers should adjust Rank values and sorting fields based on specific needs to achieve optimal user experience.