sql - how to cycle a foreign key to obtain a cycled list of elements?

Question

I've those tables:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    body TEXT NOT NULL,
    user_id INT NOT NULL REFERENCES users(id)
);

Keeping in mind I've more than 5 users with more than 100 posts each one, I want to obtain a cycle response to don't repeat the same user twice:

+---------+---------+
| id      | user_id |
+---------+---------+
| 1       | 1       |
| 23      | 2       |
| 12      | 3       |
| 50      | 4       |
| 25      | 5       |
| 23      | 1       |
| 22      | 2       |
| 77      | 3       |
...

The idea is the user_id column cycle to don't repeat twice the same value, any ideas?

Answer 1

Use row_number() for each user_id separately and use it to determine the order of results:

select id, user_id
from (
    select *, row_number() over (partition by user_id order by id) rn
    from posts
    ) s
order by rn, user_id;