Temporary instances of data for randomizing

[AndrewPurvis]

I have a database that contains information on groups of objects that, in the physical world, are discrete entities. I want to create a table that can extrapolate and hold the discrete elements, assign unique values to each, then randomize them. For my purposes, it is simple enough to think of the table in question as having only two fields: ItemCount and ItemName.

 

To use the common example of fruit, I could have the following:

4 Orange

3 Apple

2 Banana

1 Pomegranate

 

From this I want to have a table hold 10 records, using ItemName and SequenceNumber:

Orange

Orange

Orange

Orange

Apple

Apple

Apple

Banana

Banana

Pomegranate

 

From there, I should be able to use a pretty simple randomizing tool to look at the number of records that have blank SequenceNumber fields and apply the next incremented number to one of them, cycling until each has been given a number, then sort it into a stack, akin to shuffling a deck of cards, from which I can look at the first, then the second, and so on.

 

There are a number of things I plan to do once it is in that sorted order, but those are simple enough once this step is done.

 

My current thinking is something I fear would be horribly kludgy and inefficient: looping through each original record and decrementing a local variable until it is done, then looping down to the next record, populating a blank table one record at a time, but it seems there must be a better, more efficient, way.

[comment]

I am afraid you've managed to make your question incomprehensible, by [1] making it abstract, and [2] explaining more of how you want to do [something] instead of what that something is. 

 

I have -  upon reread - managed to understand you want to populate a second table with 10 records (given by the quantities in the original table). That part shouldn't be too hard.

 

But what exactly is this SequenceNumber field in the second table supposed to be? Is your purpose to sort the 10 records in random order, or to assign each record a (unique?) random number? Or both? Or something else altogether?

Edited March 5, 2015 by comment

[David Jondreau]

I agree with comment. What's the actual situation? What are the items you're talking about and what is the end goal for the user?

 

From your description, I think you're overthinking it. On the other hand, if it works, it works and a looping script is a fine thing to use.

[Josh Ormond]

Why not just insert a random number into the "Sequence Number" and then sort on that? If I read your description right, you are making a 2 step process into a 6-step process.

 

This is of course assuming I read it right and you are just trying to explode the values into a second table and then sort them in a random order.