How Can I Sort Combined MySQL Query Results from UNION Using a Rank Column?

The combined use of UNION and ORDER BY in MySQL queries to achieve sorted result grouping

In MySQL, we can combine multiple queries using the UNION operator to merge their results. When processing such queries, it can be beneficial to sort the combined results in a specific way.

Suppose you want to extract different types of records from a single table based on distance from a location. Let's say you have the following three queries:

SELECT id, add_date
FROM Table
WHERE distance = 0;

SELECT id, add_date
FROM Table
WHERE distance < 5;

SELECT id, add_date
FROM Table
WHERE distance BETWEEN 5 AND 15;

After combining these queries using UNION, you may want to sort the results under each heading, such as "Exact results", "Results within 5 kilometers", etc.

To do this, we can use a pseudo column called "Rank" for each SELECT statement. This column will contain the rank of each record within its specific grouping so that we can sort by it before applying any other sorting criteria.

Here is an example of how to achieve this:

SELECT *
FROM (
    SELECT 1 AS Rank, id, add_date
    FROM Table
    WHERE distance = 0
    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 combined_results
ORDER BY Rank, id, add_date;

In this query, results will first be sorted by the "Rank" column, which will group records based on search type (Exact, Within 5 km, or Within 5-15 km). Within each grouping, records will be sorted by "id" and "add_date".

The above is the detailed content of How Can I Sort Combined MySQL Query Results from UNION Using a Rank Column?. For more information, please follow other related articles on the PHP Chinese website!