How do I pick a random row out of a dataset?

Question

0.00/5 (No votes)

See more:

My problem is a bit more complicated than what is asked with the question of the threat. I am trying to pick a random word from a database everytime i "roll the dice". And on a second occasion, i will need to pick groups of 25 words from the same database randomly again and then I will even need to shuffle them and make new groups of 25 out of them. So yes I read a few old threats and some tutorials but it didn't help me at all. Problem is, I need something that will always give me a different word. If I use NEWID and pick a different word from the db, next time i will need to do the same again and there is a chance that same word will pop out.

I tried to use a datareader but it didn't go well. So I switched to a dataset. Everything is fine, I get the rows to a dataset and I generate a random number with a min-max value same as the rowcount of the dataset. Problem is, I can not pick a random row out of the dataset. I could set this to the unique id of the table but then again the unique id section has deleted records so there are some missing and naturally some of them are bigger numbers than the count of the rows.

Is there a more elegant way to do this? Something that can help me get my rows, assign them some numbers, perhaps indexes, then get a random number from a pool exactly same as their count, and match a random number with the rows index/line w/e to get a unique word each time. I can then substitute the already picked rows and this way there will be no recurring attendances.

I've been trying this since the morning and its been more than 12.5 hours so my brain is jelly right now. Perhaps I have entered a zone above my head as I am not a coder, just working on a small project out of necessity. Any help would be appreciated but please explain as you would to a toddler because.. you know, jelly brain... :)

Thanks in advance.

What I have tried:

I have tried using a datareader but datareader was reading forward so it was impossible to shuffle or randomize a complete content. I switched to dataset but I simply can not pick a row out of it. Rows do not have a number which I can use the single out them. I'm all confused now.

Posted 20-Dec-20 8:29am

salim çataloglu

Updated 21-Dec-20 7:00am

Add a Solution

Comments

RedDk 20-Dec-20 14:35pm

Generate a number (randomly) then use that number as a row index under a SELECT condition.

2 solutions

Solution 2

How about this:

Use your data table to create a #temp table (SQL) and then as you select records from it you delete the records from the #temp table? You'll need this table to persist between queries so either (1) make sure you don't close your connection, or (2) create a temporary use table but not a #temp type so it doesn't go away between connections.

A similar methodology would be to put the entire table in a local arraylist type object and removed items as they're used. This has a large initial overhead but only a single SQL round trip. How big the dictionary is becomes important.

Posted 21-Dec-20 7:00am

W∴ Balboos, GHB

Updated 21-Dec-20 7:07am

v2

Comments

salim çataloglu 21-Dec-20 15:43pm

It might become as big as thousands of words. I think I can set the size of the array or list to the count of the dataset however.

W∴ Balboos, GHB 21-Dec-20 15:48pm

That's small enough to download the entire table. I talk about the arraylist object because you can access elements of its (changing) length via a random function of your choice and remove it from the arraylist. Pick another, etc.,etc.

Not via random access, but I've downloaded some rather larger arrays from SQL to javaScript on the page and used it to page through a table some fixed number of records (forward, backward, wrapping). A thousand is very small vs. a beta test of the paging we conducted with 50Kb data.

salim çataloglu 21-Dec-20 16:40pm

Well I used a arraylist and it worked with a datareader too. I'm filling the arraylist with a do while dr.read loop. So I think this will work from now on.

W∴ Balboos, GHB 22-Dec-20 7:05am

If/when everything's working, mark the question "answered" so it doesn't get unnecessary attention.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-12-20T08:42:00

Solution 1

Random numbers - and NEWID's - are just that: random. That means that you can't have a random sequence that does not include duplicates, because each time you "throw the dice" every possible value has an equal chance of coming up; there is no "memory" of previous numbers.

So in order to eliminate duplicates, you need to do what you do with a pack of cards: take all possible values, shuffle them, and draw one from the top (or bottom). You then put that car don the "discards" pile, and continue to draw from the same "pile" until the time comes to shuffle all the cards and create a new pile to start working again from.

Which means in practice is that you app has to read the values from the DB and store them locally, then either use a random index into that collection (and remove the value from the collection completely), or shuffle them and use them in sequence.

Posted 20-Dec-20 8:42am

OriginalGriff

Comments

salim çataloglu 20-Dec-20 14:46pm

So what should I use to store them locally. I couldn't figure it out with a dataset. It would be easy with a control such as listview but there will be thousands of entries so... I don't know about the performance... Do you know any ways to specify and pick a row out of a dataset? I seriously can't figure out this.

OriginalGriff 20-Dec-20 14:53pm

Think about it: A dataset is a collection of datatables which can be accessed via an index, and a datatable is a collection of rows which can be accessed via another index.
So what do you think you need to store / shuffle / select from?

salim çataloglu 20-Dec-20 15:57pm

You mean, i need to figure it out with a dataset, i guess.

OriginalGriff 20-Dec-20 16:52pm

I mean "keep a collection of row indexes" ...

salim çataloglu 20-Dec-20 17:51pm

An array, I guess.

OriginalGriff 20-Dec-20 17:53pm

Yep. Or an List - the latter supports the Remove method, which an array doesn't.