Randomization in select query in SQL server
-
I have two table one is Passage (20 passage) and another one is Question table.
a) I need to get only 20 questions along with passage
b) It should be in correct order
c) Randomize (shuffle) Passage data.######### Resultant should be like this
Please help me. -
not sure about your question ...sorry.
try re posting using these guidelines
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
I don't see a question either. In addition to the link J Livingston posted, the third link in my signature explains how to ask a question.
You mention randomizing data, which is entirely possible. Taking a shot in the dark, the first thing I think of is randomizing the sort order, which can be done by ORDER BY NEWID.
-
Looking at the data and the expected outcome, isn't this just a plan inner join with an order by?
Think the OP may be getting results in a random order so no order, you get random, but a little more info would go a long way as to what the actual problem and question is.
-
As others have stated, the objective isn't very clear... That said, I think the following may be what you're looking for...
WITH
cte_Get20Questions AS (
SELECT TOP 20
q.QuestionTransId,
q.Question,
q.PassageTransId
FROM
dbo.Question q
ORDER BY
NEWID()
)
SELECT
g2q.PassageTransId,
g2q.QuestionTransId,
p.Passage,
g2q.Question
FROM
cte_Get20Questions g2q
JOIN Passage p
ON g2q.PassageTransId = p.PassageTransId
ORDER BY
g2q.PassageTransId,
g2q.QuestionTransI;As a side note... If your Questions table is large, ORDER BY NEWID() can become a rather expensive operation and there are far more efficient methods for getting 20 unique random numbers that fall within a predetermined range... I'm just not going to write out a more complex solution until I know that I've got the basic premise correct.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply