How Can I Sort Results Within Each Section When Using UNION in MySQL?

Mastering Sorted Results with MySQL's UNION and ORDER BY

Combining data from multiple SELECT statements using MySQL's UNION is powerful, but maintaining individual section sorting can be tricky. This guide demonstrates how to sort results within each section based on a specific column (like 'id' or 'add_date') while preserving the logical grouping of your UNION query.

Your scenario involves retrieving data based on different criteria from a single table, then consolidating the results using UNION under distinct headings. Here's the solution:

Introduce a "ranking" column (a pseudo-column) to each SELECT statement. This numerical value dictates the display order within each section.

<code class="language-sql">SELECT 1 AS Rank, id, add_date FROM Table
UNION ALL
SELECT 2 AS Rank, id, add_date FROM Table WHERE distance < ...</code>

Next, encapsulate this combined query within a subquery. The main query then uses this subquery as its data source, sorting first by the 'Rank' column and then by your chosen sorting criteria (e.g., 'id' or 'add_date').

<code class="language-sql">SELECT *
FROM (
    SELECT 1 AS Rank, id, add_date FROM Table
    UNION ALL
    SELECT 2 AS Rank, id, add_date FROM Table WHERE distance < ...
) AS Subquery
ORDER BY Rank, id; -- Or ORDER BY Rank, add_date;</code>

This method ensures accurate sorting within each section defined by your UNION operation, providing a clean and organized result set.

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