PostgresSQL Order by in mixed way

Question

I have a table:

Table - user

user_id     name            country         role        function
1           abc1            US              Developer   IT
2           abc3            US              Developer   IT
3           abc4            US              Developer   IT
4           abc6            US              Developer   IT
5           abc8            US              Developer   IT
6           abc9            US              Developer   IT
7           abc5            Canada          Developer   IT
8           abc2            Canada          Accountant  Finance
9           abc7            US              Accountant  Finance
10          abc10           Canada          Developer   IT
11          abc11           Canada          Accountant  Finance
12          abc12           US              Accountant  Finance

How can I order the above table so that I get record of unique combination of country, role and function.

So, the output would be like:

user_id     name            country         role        function
1           abc1            US              Developer   IT
7           abc5            Canada          Developer   IT
9           abc7            US              Accountant  Finance
8           abc2            Canada          Accountant  Finance
2           abc3            US              Developer   IT
10          abc10           Canada          Developer   IT
12          abc12           US              Accountant  Finance
11          abc11           Canada          Accountant  Finance
3           abc4            US              Developer   IT
4           abc6            US              Developer   IT
5           abc8            US              Developer   IT
6           abc9            US              Developer   IT

The above results using the following logic:

• Find the distinct combination of country, role and function
• Take the first record of the FIRST distinct combination. For ex. Row 1 has country = US, role = Developer, function = IT
• Take the first record of the Second distinct combination. For ex. Row 2 has country = Canada, role = Developer, function = IT
• Take the first record of the THIRD distinct combination. For ex. Row 3 has country = US, role = Accountant, function = Finance
• Take the first record of the FOURTH distinct combination. For ex. Row 4 has country = Canada, role = Accountant, function = Finance

Now, the unique distinct combinations are not there, so the next record would be from the FIRST distinct combination.

Is there a way to achieve this in PostgresSQL?

Answer 1

With ROW_NUMBER() window function:

SELECT t.user_id, t.name, t.country, t.role, t.function
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY country, role, function ORDER BY user_id) as rn
  FROM "user"
) t
ORDER BY rn, user_id

See the demo.
Results:

| user_id | name  | country | role       | function |
| ------- | ----- | ------- | ---------- | -------- |
| 1       | abc1  | US      | Developer  | IT       |
| 7       | abc5  | Canada  | Developer  | IT       |
| 8       | abc2  | Canada  | Accountant | Finance  |
| 9       | abc7  | US      | Accountant | Finance  |
| 2       | abc3  | US      | Developer  | IT       |
| 10      | abc10 | Canada  | Developer  | IT       |
| 11      | abc11 | Canada  | Accountant | Finance  |
| 12      | abc12 | US      | Accountant | Finance  |
| 3       | abc4  | US      | Developer  | IT       |
| 4       | abc6  | US      | Developer  | IT       |
| 5       | abc8  | US      | Developer  | IT       |
| 6       | abc9  | US      | Developer  | IT       |

In your expected results the rows with user_ids 8,9 and 11,12 are in reverse order but you did not explain why.

Answer 2

Try this:

WITH data AS (
  SELECT row_number() OVER (PARTITION BY country, role, function) as rownum, *
  FROM user
)
SELECT *
FROM data
ORDER BY rownum

rownum  user_id name  country role       function
1       11      abc11 Canada  Accountant Finance
1       7       abc5  Canada  Developer  IT
1       12      abc12 US      Accountant Finance
1       5       abc8  US      Developer  IT
2       8       abc2  Canada  Accountant Finance
2       9       abc7  US      Accountant Finance
2       10      abc10 Canada  Developer  IT
2       4       abc6  US      Developer  IT
3       1       abc1  US      Developer  IT
4       3       abc4  US      Developer  IT
5       2       abc3  US      Developer  IT
6       6       abc9  US      Developer  IT