Postgresql random row assignment

Question

I have got 2 tables

Student

|---------------------|------------------|
|         id          |     studentname  |
|---------------------|------------------|
|          1          |     studentA     |
|---------------------|------------------|
|          2          |     studentB     |
|---------------------|------------------|
|          3          |     studentC     |
|---------------------|------------------|
|          4          |     studentD     |
|---------------------|------------------|  

Chair

|---------------------|------------------|
|      id             |     chairname    |
|---------------------|------------------|
|          1          |     chairA       |
|---------------------|------------------|
|          2          |     chairB       |
|---------------------|------------------|
|          3          |     chairC       |
|---------------------|------------------|

I need a query to randomly assign a student to a chair in postgresql. Note that there can be more chairs than students or vice versa. In case there is more student than chairs, students not assigned will have null value in chair.

For the above tables we should have a result like

|---------------------|------------------|
|      studentname    |     chairname    |
|---------------------|------------------|
|          studentA   |     chairC       |
|---------------------|------------------|
|          studentB   |     chairA       |
|---------------------|------------------|
|          studentC   |     chairB       |
|---------------------|------------------|
|          studentD   |     NULL         |
|---------------------|------------------|

OR

|---------------------|------------------|
|      studentname    |     chairname    |
|---------------------|------------------|
|          studentA   |     chairA       |
|---------------------|------------------|
|          studentB   |     chairC       |
|---------------------|------------------|
|          studentC   |     NULL         |
|---------------------|------------------|
|          studentD   |     chairB       |
|---------------------|------------------|

Any idea how this can be done in postgresql?

Tim Biegeleisen · Accepted Answer · 2019-07-15 15:24:24Z

2

You could try using ROW_NUMBER with a random ordering to assign student IDs to the various chairs:

WITH cte1 AS (
    SELECT chairname, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
    FROM Chair
),
cte2 AS (
    SELECT studentname, ROW_NUMBER() OVER (ORDER BY id) rn
    FROM Student
)

SELECT
    s.studentname,
    c.chairname
FROM cte2 s
LEFT JOIN cte1 c
    ON s.rn = c.rn;

Demo

Note that I actually generate a row number sequence across both tables, including the Student table. This is to ensure that we always compare row number values which would match between both tables.

Edit: Replace LEFT JOIN with FULL OUTER JOIN to generally handle the case where the number of students exceed chairs or vice-versa.

edited Jul 15, 2019 at 15:24

answered Jul 15, 2019 at 14:01

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S-Man Over a year ago

That is really a nice solution

forpas Over a year ago

Since there is the case of more chairs than students, I think that a FULL JOIN is more suitable.

Kevin Joymungol Over a year ago

Great solution. Works. Thanks

Collectives™ on Stack Overflow

Postgresql random row assignment

1 Answer 1

Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related