CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005random
JamshaidRiaz
JamshaidRiaz

Reputation: 111

I want to select random questions from a Table from each catogories

SQL Server 2005 query needed, please help.

I have a table which have questions and all questions are categorized TopicWise wise.

Table Questions

And another table which Contains topic and its weightage of number in the Subject

Table Topics

Now, I need to select 50 random questions from Table QUESTIONS which are based on the weightage of the Topic like out of 50 random selected questions there must be 

5 questions from Subtopic_Id=1 and    
15 questions from Subtopic_Id=2 and
10 questions from Subtopic_Id=3 and
10 questions from Subtopic_Id=4 and
10 questions from Subtopic_Id=5

See Second image (Table Topics)

and the number of topics and their weightage may vary in different subjects, so union statement will be a bad idea for a dynamic query.

I even have not figure out a single bit of line of code for this selection.

Upvotes: 1

Views: 757

Answers (3)

M.Ali
M.Ali

Reputation: 69554

You can use column values in your TOP clause. Not a common knowledge but it can be done.

You can make use of TopicWeightage column in your TOP clause.

It can be done but its just there are very rare chances you would ever write a query like this. you happen to be in such a situation hence the solution would be something like.....

SELECT Q.ExamQ_ID
FROM TopicTable t 
              CROSS APPLY ( 
                           SELECT TOP (t.TopicWeightage) ExamQ_ID
                           FROM Questions
                           WHERE SubjTopicID =  t.SubjTopic_ID
                           ORDER BY NEWID()  
                           ) Q(ExamQ_ID)

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

select  * from (select Top 5 *
 from questions where subtopic_id=1
order by NEWID())a1

 union

select * from (

select Top 10 * 
 from questions where subtopic_id=2 
from dbo.spegg_AtgPrice_Diff
order by NEWID())
a2

and so on

Upvotes: 0

Kevin
Kevin

Reputation: 7309

I don't really see any other way than using dynamic sql and, for each subtopic, doing a query with an order by rand and get the top x number.

Upvotes: 0

Related Questions