CREATING duplicate records for a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CREATING duplicate records for a view

Author

Topic

RiverGuy
Starting Member

9 Posts

Posted - 2002-10-31 : 20:50:46

Hi all. This is my first post here, and I pose a question.

This is what I have:

Name Amount
---- ------
Bill 1
Fred 3
Frank 5

This is what I would like for my view:
Name
----
Bill
Fred
Fred
Fred
Frank
Frank
Frank
Frank
Frank

I would like one record for each person, per their amount. I am doing this to select a random record with the newid() function, and I need a person with a higher amount to be chosen more frequently.

I cannot for the life of me think of a way to do this. Can anyone off some tips?

Thanks in advance.

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-10-31 : 23:04:26

Hi River,

Only way to do do this would be (unless some else can think of a better way) by writing a stored procedure which would populate a temp table with the required result set.

Let us know what you think abt it, i would be more then happy to give you a hand in writing stored proc if u need me to.

cheers,

Samrat

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-31 : 23:16:37

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20925

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 00:23:21

Thanks guys. I am open to that suggestion. I am going to do some thinking and write back with the procedure I'm using now, and see if the way you suggest seems faster to y'all.

Thanks.

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-01 : 10:18:10

This sounds like a weighted selection problem.

One way:

Derive a 'count to this row' for each row
Derive a TOTAL for all rows
select a random number between 0 and the total
Find the record who's 'count to this row' that random number.

--------
alter table Mytable add RowTotal INT NULL
declare @t int
set @t = 0
update MyTable set @t = RowTotal = @t + Amount -- Now each column as a

select @t = sum(Amount) FROM MyTable -- Total for ALL rows

declare @MyRandom INT

set @MyRandom = CAST (rand() * @t AS INT) -- Random number between 0 and sum(Amount)
print @MyRandom -- Sanity check for debugging
-- Now get the record of interest

SELECT Top 1 * -- Just the top row
FROM MyTable
WHERE RowTotal > @MyRandom -- Don't include rows below MyRandom
ORDER BY RowTotal ASC -- Better set the order ascending !

The record returned by the select should have a distribution frequency proportional to each row's Amount. If 1000 selections are performed, each row should be selected approximately 1000 * (Amount / Total) of the time.

Sam

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 11:18:17

Sam that's what I'm essentially doing now, but I was originally just doing a row by row comparsion for amount with a Random number in VB and comparing it against the running total of the amount. But the row by row comparison seemed to slow for that, so I just threw every record into a dynamic array, which is pretty fast.

But I thought I might be able to get it even more efficient, and make the VB coding simpler. That's why I asked the original question.

And since I am not a huge database guy, I'm not the best SP person, so I may give your response a try. Seems like it would be faster than the method I'm using now.

Another question: Would "SELECT Top 4" Select the first four records if I needed to get 4?

Thanks

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 12:21:50

Sam, your solution worked great. It was very fast in Query Analyzer and seemed to produce good results. Thanks for that tip.

Now I am just trying to figure out how to use the result set in VB. If I make a recordset with that text (without the alter table because it already is), I get can't do it with connection closed.

Thanks again.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-01 : 12:42:47

Put SET NOCOUNT ON at the very beginning of the code, and it should work. The rowcount messages are making ADO barf.

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 13:45:46

Tried that robvolk. It still claims 'the object is closed'

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 14:06:14

Hey Rob, I got it working now. Thanks for that. Part of the reason why it was not still not working with the NOCOUNT is because I had switched to just a recordset with the SP's text minus the 'Create Procedure' clause to try and troubleshoot it earlier. It works great now with just the SP itself.

Thanks everyone for your help!@

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 15:03:58

The final question I have is to whether I need to seed the random number. I can't find any sort of time function that will work. Any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-01 : 16:28:03

I was gone all day, and am glad you got somewhere with the help of Rob.

If you're still interested in the Top 4, let me know. Don't use Top 4 though, you won't get a random distribution. There may be a way to modify the procedure to return 'N' random choices.

I've been interested in the seed problem myself and was glad you asked about it. You should try the "Forum Search" link above to see what others have posted about the rand() function and seed in other posts. Try search terms "rand seed"

I haven't found a definitive solution for the ranf() seed problem. What I have read indicates that the granularity of getdate() is too coarse (300 samples / second) to be a useful seed. Getdate() is the obvious choice for a seed. I don't know of any other starting point.

There is probably a way to take a large prime number, and multiply by GETDATE() seconds/milliseconds, then XOR in the datepart to yeild a pretty good seed. Cast it to varchar, then add eye of newt and wing of bat to get a good seed. Confidence in any seed formula should be based on the testing that has been applied to the results, not how interesting is the seed formula. That's where my interest with this problem ends. Some of the posts on this subject start with great seed ideas, only to end with frustration with the test results.

I have not seen any description on the internal seed used by rand() if none is supplied. I've had some experiences that indicate the internal seed is pretty good, and on the other hand, I've had other experiences that yielded predictable (not random) results.

Sam

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-01 : 18:15:21

Hey Sam, thanks for your reply. As far as the seed, maybe I will make it a parameter and supply it from VB when I call it.

If worse comes to worse, I might run the procedure 4 times to get 4 records.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources