CODEWITHSUNDEEP
postgresql
Kevin Joymungol
Kevin Joymungol

Reputation: 1814

Postgresql random row assignment

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?

Upvotes: 0

Views: 81

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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;

enter image description here

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.

Upvotes: 2

Related Questions