CODEWITHSUNDEEP
sqlsql-serverlinqsql-server-2008linq-to-sql
Francis P
Francis P

Reputation: 13665

SQL RANDOM ORDER BY ON JOINED TABLE

I have 2 tables: Persons(idPerson INT) and Questions(idQuestion INT).

I want to insert the data into a 3rd table: OrderedQuestions(idPerson INT, idQuestion INT, questionRank INT)

I want to assign all the questions to all the persons but in a random order.

I thought of doing a CROSS JOIN but then, I get the same order of questions for every persons.

INSERT INTO OrderedQuestions
    SELECT idPerson, idQuestion, questionRank FROM Persons 
    CROSS JOIN
    (SELECT idQuestion,ROW_NUMBER() OVER (ORDER BY NEWID()) as questionRank
    FROM Questions) as t

How can I achieve such a random, distinct ordering for every persons?

Obviously, I want the solution to be as fast as possible. (It can be done using TSQL or Linq to SQL)

Desired results for 3 persons and 5 questions:

   idPerson         idQuestion         questionRank
1.  1                18                  1
2.  1                14                  2
3.  1                25                  3
4.  1                31                  4
5.  1                2                   5
6.  2                2                   1
7.  2                25                  2
8.  2                31                  3
9.  2                18                  4
10. 2                14                  5
11. 3                31                  1
12. 3                18                  2
13. 3                14                  3
14. 3                25                  4
15. 3                2                   5

I just edited the results (Since the IDs are autogenerated, they can't be used to order the questions).

Upvotes: 3

Views: 420

Answers (1)

jTC
jTC

Reputation: 1350

This could probably be written more efficently, but it meets all the reqs.

SELECT 
    idperson, 
    idQuestion,
    ROW_NUMBER() OVER (PARTITION BY personid ORDER BY ordering) as questionRank 
FROM (
    SELECT idperson, idQuestion, ordering 
    FROM person
    CROSS JOIN 
    (
        SELECT idQuestion, NewID() as ordering FROM Question
    ) as t
) as a
order by personid, QuestionRank

Upvotes: 2

Related Questions