Merge two sorted results with intermittently of one on two

Question

Which way can I to use for merge two sorted result.

Example :

SELECT * FROM (VALUES (1),(2),(3),(4)) AS X(a);
┌───┐
│ a │
├───┤
│ 1 │
│ 2 │
│ 3 │
│ 4 │
└───┘
(4 rows)

And

SELECT * FROM (VALUES ('A'),('B'),('C'),('D')) AS X(a);
┌───┐
│ a │
├───┤
│ A │
│ B │
│ C │
│ D │
└───┘
(4 rows)

And the result

┌───┐
│ a │
├───┤
│ A │
│ 1 │
│ B │
│ 2 │
│ C │
│ 3 │
│ D │
│ 4 │
└───┘
(4 rows)

I try some solution creating a new temporary column with modulo for each result but I block for the merge and I am not sure is really performing.

Thanks you for your advice.

S-Man · Accepted Answer · 2018-09-26T09:30:14.627

demo: db<>fiddle

select a from (
    SELECT a::text, row_number() OVER() as rowcount, 2 as sort_table 
    FROM (VALUES (1),(2),(3),(4)) AS X(a)

    UNION ALL

    SELECT a::text, row_number() OVER(), 1 
    FROM (VALUES ('A'),('B'),('C'),('D')) AS X(a)
) s

ORDER BY 
    rowcount, sort_table

You have to add some metadata to find the right sort order. First I added the row_number() window function (https://www.postgresql.org/docs/current/static/tutorial-window.html) to both tables. This adds a unique row number per row. So this works as first order column. Now I am able to order by row number: First come the first rows of all tables followed by the second rows of the tables, ... Now you have two "first" rows in you result which have to be ordered. If you want the rows of the second table you can achieve this by a constant value. I named it sort_table.

score 1 · Answer 2 · answered Sep 26 '18 at 09:18

Well, it can be done using ROW_NUMBER, but why do you need that?

select a
from 
 (
   SELECT a, 2 as flag, row_number() over (order by a) as rn 
   FROM (VALUES (1),(2),(3),(4)) AS X(a)
   union all
   SELECT a, 1 as flag, row_number() over (order by a) as rn
   FROM (VALUES ('A'),('B'),('C'),('D')) AS X(a)
 ) as dt
order by rn, flag

Of course you need matching datatypes in both Selects.

score -1 · Answer 3 · answered Sep 26 '18 at 12:40

with cte(a, RowsNumber) as ( SELECT CONVERT(varchar(120), a) [a], ROW_NUMBER() over(order by a) [RowsNumber] FROM (VALUES (1),(2),(3),(4)) AS X(a) union all SELECT a, ROW_NUMBER() over(order by a) [RowsNumber] FROM (VALUES ('A'),('B'),('C'),('D')) AS X(a)) Select a from cte order by RowsNumber

Merge two sorted results with intermittently of one on two

3 Answers3