The gender with the minimum number of students should only pair with opposite gender. The remaining students of the opposite gender can pair among themselves.
1. One student can only be paired with another student.
Ie, if (stud1,stud2) pair exists, there cannot be another pair (Stud1,Stud3) or (Stud3,Stud1)
2. (Stud1,stud2) pair is same as (Stud2,stud1) pair. So If your sql returns a pair (Stud1,Stud2), there shouldn’t be another pair (Stud2,Stud1)
sel * from RX_WK.STUDENTS
You can assign a row_number sorted by gender and split in two groups.
Assuming you want to pair randomly:
WITH cte AS
ROW_NUMBER() OVER (ORDER BY gender, id) - 1 AS rn, -- row number over all rows
(COUNT(*) OVER () + 1) / 2 AS pairs -- calculating the number of pairs
RANDOM(1,100000) AS rnd -- for random pairing (can't be used directly in OLAP-functions)
) AS dt
FROM cte AS t1 LEFT JOIN cte AS t2 -- when odd number of rows one row can't be paired
ON t1.rn = t2.rn - t2.pairs -- adjust row number to match
WHERE t1.rn < t1.pairs -- only first half of rows
Thank you very much Dieter it is working fine.
Complexity increses within the question .
1. Group shouldn’t be of two people having the same last name unless there are no other options.
2. If a student is not having any member to team up with, he/she should be partnered with ‘teacher’
If there are 3 female students and 6 male students, then all the females can be paired with male students. There will be 3 male students left out of which 2 will pair among themselves and the 1 male student who is still left out will make a group with ‘teacher’ as partner. Note that the students who are paired do not share the same last name.
here i think we have join the table.
sel * from RX_WK.INSTRUCTOR
show table RX_WK.INSTRUCTOR_COURSE
You probably run an older version of Teradate where you need to add the column list like
WITH cte (col1,col2,.., rn, pairs) AS
There's no way to avoid pairing students with the same name using this randomized approach, but of course the RANDOM will reduce probability (Btw, I just noticed that I didn't use
rnd in my ROW_NUMBER calculation, should be
ROW_NUMBER() OVER (ORDER BY gender, rnd) - 1 AS rn).
You might check for same name rows adding
AND CASE WHEN t1.last_name = t2.last_name THEN 1/0 ELSE 0 END = 0, the query will fail with a division by zero error in that case and you have to rerun it.