terdata query.

Database
Enthusiast

terdata query.

 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 

id first_name last_name dob gender

1 1 Akshay Kumar 8/15/1967 M

2 2 Sharukh Khan 11/2/1965 M

3 3 Amitabh Bhachan 10/11/1942 M

4 4 Priyanka Chopra 7/18/1982 F

5 5 Parineeti Chopra 10/22/1988 F

6 6 Varun Dhawan 4/24/1987 M

7 7 Salman Khan 12/27/1965 M

8 8 Aamir Khan 3/14/1965 M

9 9 Kajol Devgan 8/15/1974 F

10 10 Ajay Devgan 4/2/1969 M

11 11 Deepika Padukone 1/5/1986 F

Thank you.

5 REPLIES
Senior Apprentice

Re: terdata query.

You can assign a row_number sorted by gender and split in two groups.

Assuming you want to pair randomly:

WITH cte AS 
(
SELECT
dt.*,
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
FROM
(
SELECT t.*,
RANDOM(1,100000) AS rnd -- for random pairing (can't be used directly in OLAP-functions)
FROM your_table
) AS dt
)
SELECT *
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
Enthusiast

Re: terdata query.

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’

Expl:

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

ID NAME  CURR_IND

1  Sachin Tendulkar Y

2   Rahul Dravid   Y

3  Robin Singh    N

4  Saurav Ganguly   Y

5 Sunil Gavaskar  N

Enthusiast

Re: terdata query.

show table RX_WK.INSTRUCTOR_COURSE

INSTRUCTOR_ID INTEGER,

COURSE_ID INTEGER

show table RX_WK.STUDENT_COURSE 

 STUD_ID INTEGER,

 COURSE_ID INTEGER

These tables can be used for join.


Re: terdata query.

Hi dnoeth/Kuldeep,

I have tried with below query its giving me error as below.

Syntax error: expected something between the word 'cte' and the 'AS' keyword.

 Do I need to put "with recursive cte AS"

WITH cte AS

 (

   SELECT

      dt.*,

      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

   FROM

    ( 

      SELECT t.*, 

         RANDOM(1,100000) AS rnd -- for random pairing (can't be used directly in OLAP-functions)

      FROM STUDENTS t

    ) AS dt

 ) 

SELECT *

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

Please help.

Senior Apprentice

Re: terdata query.

@GTeradata:

You probably run an older version of Teradate where you need to add the column list like WITH cte (col1,col2,.., rn, pairs) AS

@Kuldeepg92:

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.