Select random records from a table based on condition

Question

Hi all I have a table which holds data and the table is as follows my requirement is to load some random questions based on the total time against DifId. If against 1 30 minutes was set I need to load question randomly so that total time should be equal to 30. Id Description Time DifId 1 ABC 1 1 2 ABC 5 1 3 ABC 9 1 4 ABC 10 1 5 ABC 10 1 6 ABC 2 1

Nilesh Shah · Answer

@Haider Ali I am not merging any table I did not write whole SQL script as I didn't want to I just wanted to say use CURSOR and fetch records until equal to sum of time is what you want something like( will not work as it is though): DECLARE random_cursor CURSOR FOR SELECT * FROM #randomTempTable OPEN random_cursor FETCH NEXT FROM random_cursor INTO @var1 @var2 WHILE @@FETCH_STATUS = 0 BEGIN --do here insert into #resultTable until sum of time is equal to what you want END CLOSE random_cursor DEALLOCATE random_cursor --output result select * from #resultTable

Nilesh Shah · Answer

I think it's possible but not using only 1 query you may have to write a stored procedure. here I tell you some steps: first of all to select random data from a table use following query here I assume DifId is your input: select * from RandomQuestions into #randomTempTable ORDER BY NEWID() order by newid() - this will give you random records everytime you fire this query then once you have the able records get in a temp table or cursor loop thru that table/cursor and insert records in another temp table until the sum of time is equal to your input(30 minutes in your case): --cursor on #randomTempTable insert into #resultTable select top 1 * from CURSOR the above is only psuedo code I hope this helps!

Haider Ali · Answer

Hello Nilesh!!! Here r u merging #randomtemptable and #resulttable? mean to say how to access data from #randomtemptable???