Is it possible to output the result of a union of two subqueries in alternating order?

user_3237785 0
Viewed 79

Lets say I have two subqueries in a UNION statement like so:

(
    SELECT * 
    FROM users
    ORDER BY registration_date
) 
UNION ALL
(
    SELECT *
    FROM food
    ORDER BY popularity
)

The output is the following:

Bob
Alice
Steve
Mark
...
Sandwich
Pizza
Burger
Fries
...

Is it possible to output them in an alternating fashion, such that the output is:

Bob
Sandwich
Alice
Pizza
Steve
Burger
Mark
Fries
...

Each query output is thousands of items.

window-functions union sql-order-by mysql sql
3 Answers

You could use row_number() if you are running MySQL 8.0:

(select name, 1 src, row_number() over(order by registration_date) rn from users)
union all 
(select name, 2, row_number() over(order by popularity) from food)
order by rn, src

In each unioned subquery, we use row_number() to rank the records, and add another column, called src to identify from which table the record comes from.

Then all that is left to do is order by the assigned row_number(), using the additional column to alternate the records.

Note that I modified your query to enumerate the columns being selected in the subqueries; select * is generally not a good practice, especially with union all, which requires both datasets to have the same number of columns (with equivalent datatypes).

user_106767160

Just in case you are not using MySQL 8+, you can still simulate ROW_NUMBER using a correlated count query:

(
    SELECT 1 AS idx, name,
        (SELECT COUNT(*) FROM users u2 WHERE u2.registration_date < u1.registration_date) rn
    FROM users u1
)
UNION ALL
(
    SELECT 2, name,
        (SELECT COUNT(*) FROM food f2 WHERE f2.popularity < f1.popularity) rn
    FROM food f1
)
ORDER BY
    rn,
    idx;
user_18632290 
DECLARE @id INT;
SET @id :=0;

SELECT id,t.* FROM(
SELECT  u.*,(@id:=@id+1) AS id

   FROM users u
    ORDER BY registration_date
) 
UNION ALL
(
    SELECT f.*,(@id:=@id+2) AS id

    FROM food f 
    ORDER BY popularity
)t
ORDER BY id;
user_128885720
Related