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).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With