How Can I Sort Results Within Categories After Using UNION in MySQL?

Sort UNION query results in MySQL

When using the UNION operator in MySQL to process data from multiple data sources, sorting results can present challenges, especially when results need to be sorted within a specific category. This problem usually occurs when extracting data from a single table based on different criteria (for example, distance filtering of a search query).

Problem Description

In the given scenario, three separate queries extract data based on different distance conditions:

1. Exact match search
2. Distance within 5 kilometers
3. Distance within 5-15 kilometers

Then use UNION to combine these results and display them under the appropriate headings (for example, "Exact results" and "Results within 5 kilometers"). The user wants to sort the results based on ID or add_date, but adding an ORDER BY clause at the end of the merge query sorts all results overall, ignoring the need to sort by title.

Solution using Rank pseudo-column

In order to sort the results within each category while merging the results, you can introduce a pseudo column called "Rank" in each select query. This column will be used to sort by before sorting by the actual criteria. The following code illustrates this approach:

SELECT *
FROM (
    SELECT 1 AS Rank, id, add_date FROM Table WHERE <精确匹配条件>
    UNION ALL
    SELECT 2 AS Rank, id, add_date FROM Table WHERE distance < 5
    UNION ALL
    SELECT 3 AS Rank, id, add_date FROM Table WHERE distance BETWEEN 5 AND 15
) AS CombinedResults
ORDER BY Rank, id DESC, add_date DESC;

In this query, the Rank pseudo-column assigns values ​​of 1, 2, and 3 to the three queries respectively. The ORDER BY clause sorts the results first by Rank, then by ID and add_date in descending order. This approach allows you to sort the results within each heading while maintaining category distinction.

Please note that <精确匹配条件> needs to be replaced with your exact match search criteria. This solution assumes your table is named Table and contains id, add_date, and distance columns. Make appropriate adjustments based on your actual table structure and conditions.

The above is the detailed content of How Can I Sort Results Within Categories After Using UNION in MySQL?. For more information, please follow other related articles on the PHP Chinese website!