Is there a way to alternate the order of a result based off one id while keeping groupings based off another id in SQL?

Question

I have this query in Postgresql:

(SELECT q.question, q.category_id, a.id, a.question_id, a.answer
    FROM questions q, answers a
    WHERE q.id = a.question_id
    AND category_id = 1
    AND question_id
    BETWEEN (SELECT property FROM users WHERE email = '[email protected]')
    AND (SELECT property FROM users WHERE email = '[email protected]') + 14)
    
    UNION
    
(SELECT q.question, q.category_id, a.id, a.question_id, a.answer
    FROM questions q, answers a
    WHERE q.id = a.question_id
    AND category_id = 2
    AND question_id
    BETWEEN (SELECT laws FROM users WHERE email = '[email protected]')
    AND (SELECT laws FROM users WHERE email = '[email protected]') + 16)
    
ORDER BY question_id, id

that currently returns results in this format:

+-----------------------------+-------------+-------------+--------+
|          question           | category_id | question_id | answer |
+-----------------------------+-------------+-------------+--------+
| What color is the sky?      |           1 |          16 | blue   |
| What color is the sky?      |           1 |          16 | green  |
| What color is the sky?      |           1 |          16 | purple |
| What color is the sky?      |           1 |          16 | red    |
| What color is a firetruck?  |           1 |          17 | orange |
| What color is a firetruck?  |           1 |          17 | teal   |
| What color is a firetruck?  |           1 |          17 | red    |
| What color is a firetruck?  |           1 |          17 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
+-----------------------------+-------------+-------------+--------+

What I want to do is alternate the order based off the category_id, so the category id would be alternating like this: 1,2,1,2, but I want to keep the groups based off the question_id. So the result would look like this:

+-----------------------------+-------------+-------------+--------+
|          question           | category_id | question_id | answer |
+-----------------------------+-------------+-------------+--------+
| What color is the sky?      |           1 |          16 | blue   |
| What color is the sky?      |           1 |          16 | green  |
| What color is the sky?      |           1 |          16 | purple |
| What color is the sky?      |           1 |          16 | red    |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is dirt?         |           2 |          18 | green  |
| What color is a firetruck?  |           1 |          17 | orange |
| What color is a firetruck?  |           1 |          17 | teal   |
| What color is a firetruck?  |           1 |          17 | red    |
| What color is a firetruck?  |           1 |          17 | green  |
+-----------------------------+-------------+-------------+--------+

I've tried to using ORDER BY row_number() OVER (PARTITION BY t.category_id ORDER BY t.category_id) but that just results in each being alternated without being grouped by question_id

Answer 1

I think you want the following order by clause:

order by 
    rank() over(partition by category_id order by question_id),
    question_id,
    id

Basically this interleaves the categories/questions tuples.

Notes:

• use standard, explicit joins (from ... join ... on) rather than old-school, implicit joins (from ..., ... where ...); this is prehistoric syntax, that should not be used in new code

• it is rather likely that your query could be simplified to not use union; if you were to ask another question with sample data, and desired results, one might be able to suggest