Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-16-2016
02:06 AM

08-16-2016
02:06 AM

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 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-16-2016
09:13 AM

08-16-2016
09:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-16-2016
11:28 PM

08-16-2016
11:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-17-2016
12:02 AM

08-17-2016
12:02 AM

Re: terdata query.

show table RX_WK.INSTRUCTOR_COURSE

INSTRUCTOR_ID INTEGER,

COURSE_ID INTEGER

show table RX_WK.STUDENT_COURSE

show table RX_WK.STUDENT_COURSE

STUD_ID INTEGER,

COURSE_ID INTEGER

These tables can be used for join.

These tables can be used for join.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-17-2016
04:27 AM

08-17-2016
04:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-17-2016
07:38 AM

08-17-2016
07:38 AM

Re: terdata query.

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.