Select random records from a table based on condition

Chaitanya

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah8y ago
@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
0
Haider Ali8y ago
Hello Nilesh!!!
Here r u merging #randomtemptable and #resulttable? mean to say how to access data from #randomtemptable???
0
Nilesh Shah8y ago
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!
0