Merging two tables with interleaving the result of select queries of both tables in a proportion

user_6182303 0
Viewed 91

I have two tables

CREATE TABLE Posts
(
Id BIGSERIAL NOT NULL,
Content TEXT NOT NULL
);

CREATE TABLE Ads
(
Id BIGSERIAL NOT NULL,
Content TEXT NOT NULL
);

I need to combine these two tables in a proportion and apply ORDER BY and OFFSET. 1 row from Ads table in each 10 rows from Posts table Is there any way I can accomplish this?.

The final rows should be 10 posts - 1 ad - 10 posts - 1 ad. How can achieve the merging two tables with interleaving the result of different selects

Normal UNION will merge all rows without interleaving

SELECT * FROM (SELECT Id, Content, 'Post' AS Type FROM Posts
UNION 
SELECT Id, Content, 'Ads' AS Type FROM Ads) AS sel
LIMIT 10;
union sql-order-by postgresql sql
1 Answers

One option is to use row_number():

select id, content, type
from (
    select id, content, 'post' type, (row_number() over(order by id) - 1) / 10 rn 
    from posts
    union all select id, content, 'ads', row_number() over(order by id) - 1 
    from ads
)
where rn <= 1
order by rn, type desc

The trick is to divide the row numbers of the posts table by 10 - so each group of 10 consecutive rows get the same rank. Then, the outer query sorts by rank, then by descending type. This puts the first 10 posts first (with rank 0), then 1 ad, and so on. The rank can also be used to limit the number of groups.

user_106767160
Related