How Can I Sort Results Within Separate Sections of a MySQL UNION Query?

Sort results by section in MySQL UNION query

Introduction:

When using multiple SELECT statements combined with UNION to query data, you need to sort the results appropriately by title while maintaining the independence of each query.

Question:

One scenario is that three SELECT queries retrieve data based on different distance criteria for website search. The first query retrieves data for exact location searches, the second query retrieves data within 5 kilometers, and the third query retrieves data within 5-15 kilometers. Query results are combined using UNION and displayed on a page with a title. However, the user wants to sort the results under each heading based on id or add_date.

Solution:

To achieve this, MySQL introduces a pseudo column named "rank" for each SELECT statement. This pseudo column can be used to initially sort the results before applying other sorting criteria. The following modified query demonstrates this approach:

SELECT *
FROM (
    SELECT 1 AS Rank, id, add_date FROM Table
    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 RankedResults
ORDER BY Rank ASC, id DESC, add_date DESC;

Explanation:

1. Pseudo column "Rank": In each SELECT statement, a pseudo column named "Rank" is introduced and assigned unique values ​​(1, 2, and 3). This column represents the order or grouping of results.

2. Initial sorting by "Rank": UNION results are initially sorted by the "Rank" column in ascending order. This ensures that results are grouped according to their respective titles ("Exact results", "Results within 5 km" and "Results within 5-15 km").

3. Additional sorting by "id" and "add_date": Within each grouping (determined by "Rank"), the results are further sorted by "id" and "add_date" in descending order.

Note:

This method uses the UNION ALL operation, which contains duplicate rows in the combined result. If you don't need duplicate rows, you can use the UNION operation, which will eliminate duplicates.

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