How Can I Sort MySQL UNION Results by Groups Using ORDER BY?

Mastering MySQL UNION: Sorting Results Within Groups Using ORDER BY

MySQL's UNION operator combines results from multiple SELECT statements. However, ordering these combined results can be tricky, especially when you need to sort within each individual group. This guide demonstrates a clever method using a pseudo-column to achieve this.

The solution involves these steps:

1. Introduce a Pseudo-Column: Add a column (we'll call it 'Rank') to each of your individual SELECT statements. This column acts as a group identifier, assigning a unique value to each group.

2. Construct the UNION: Combine your modified SELECT statements using UNION ALL (or UNION if you need to remove duplicates).

3. Order with the Outer Query: Wrap your UNION within an outer SELECT statement. Use the 'Rank' column as the primary sorting criterion in the ORDER BY clause of this outer query.

4. Refine the Sort: After sorting by 'Rank' (grouping), add your secondary sorting criteria (e.g., 'id' or 'add_date') to further order the results within each group.

Illustrative Example:

Let's say you have a table named 'table' with columns 'id' and 'add_date'. The following query demonstrates the technique:

<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 < 10  -- Example condition for a group
) AS combined_results
ORDER BY Rank, id, add_date;</code>

This query adds a 'Rank' column (1 for the first group, 2 for the second), then uses the outer SELECT to sort first by 'Rank' (grouping the results) and then by 'id' and 'add_date' within each group. This ensures proper ordering within each subset of data generated by the individual SELECT statements. Remember to replace table and the conditions within the subqueries with your actual table and filtering logic.

The above is the detailed content of How Can I Sort MySQL UNION Results by Groups Using ORDER BY?. For more information, please follow other related articles on the PHP Chinese website!