i have tried 8 million way to do this 2

[aspnet98]

I have a table in sql server 2000. it looks like this:

id username value week
1 john 0 1
2 john 1 1
3 john 0 1
4 john 1 1
5 john 1 1
6 john 0 1
7 john 1 1
8 john 1 1
9 john 0 1
1 craig 1 1
2 craig 0 1
3 craig 1 1
4 craig 0 1
5 craig 1 1
6 craig 1 1
7 craig 0 1
8 craig 1 1
9 craig 0 1

I need to replace the inverse of TOP 5, in this case it would be four. I need to replace those 4 with a value of 4 instead of 1. The problem is I need to replace the 4's randomly for each username.........

i give up i tried so many 5 step processes.....

i am not sure it is possible.......

i would expect the results to look like this....

id username value week
1 john 0 1
2 john 1 1
3 john 4 1
4 john 1 1
5 john 4 1
6 john 0 1
7 john 1 1
8 john 4 1
9 john 4 1
1 craig 1 1
2 craig 4 1
3 craig 4 1
4 craig 0 1
5 craig 1 1
6 craig 1 1
7 craig 4 1
8 craig 1 1
9 craig 4 1

 

[amrita418]

I know cursors are bad and should be avoided whenever possible but in this case could not think of any other easy way to do it.

I mean, since the id for a given username should be picked randomly, there is chance that the same id is picked twice in which case additional row should be updated.

If the number of rows in the table is not huge, performance should not be of concern.

declare 
@v_username varchar(10),
@v_test    int
set nocount on
declare c1 cursor for select username
                      from   TableA
                      group by username
                      having sum(case when value in (0, 1) then 1 else 0 end) > 5

open c1
set @v_username = ''
set @v_test = 0
fetch next from c1 into @v_username
while @@fetch_status = 0
begin
    while @v_test = 0
    begin
        update  TableA
        set     value = 4
        where   username = @v_username
                and id = ceiling(8 * rand(checksum(newid())) + 1)
        IF ((select count(*) from TableA where username = @v_username and value != 4) <= 5)
           set @v_test = 1
    end
fetch next from c1 into @v_username
set @v_test = 0
end
close c1
deallocate c1

Regards,
AA

 

[aspnet98]

AA:
It is still executing in sql analyzer.....15 mins and counting. There are only 1400 rows it needs to update. Anything else I can do, why would it take this long. I had to kill the job........

 

[amrita418]

I tested the code on 400 rows and it ran in less a second. My table had no indexes. Do you?

Here a same code with very minute changes. See if this works for you.

declare 
@v_username varchar(10),
@v_test    int,
@v_count int,
@v_id int

set nocount on
declare c1 cursor for select username
                      from   TableA
                      group by username
                      having sum(case when value in (0, 1) then 1 else 0 end) > 5

open c1
set @v_username = ''
set @v_test = 0
set @v_id = 0
fetch next from c1 into @v_username
while @@fetch_status = 0
begin
    while @v_test = 0
    begin
	set @v_count = 0
        set @v_id = ceiling(8 * rand(checksum(newid())) + 1)
        update  TableA
        set     value = 4
        where   username = @v_username
                and id = @v_id

        set @v_count = (select count(*) from TableA where username = @v_username and value != 4)
        --print 'The count is ' + convert(varchar, @v_count) + ' id was ' + convert(varchar, @v_id)
        If (@v_count <= 5)
           set @v_test = 1
        set @v_id = 0
        
    end
fetch next from c1 into @v_username
set @v_test = 0
set @v_id = 0
end
close c1
deallocate c1

Do keep us posted.

Vongrunt,

I used your logic to generate random numbers. I know I could just use rand() in this case since only one row is updated at a time but wanted to be sure.

Can you suggest anything to make it return a more random value between 1 and 9. That might save some looping here.

Regards,
AA

 

[vongrunt]

Yup, one row is updated at a time and RAND() without seed is OK.

I think this can be done more efficiently with WHILE loop and set-based random updates (all usernames at once, for those who still have >5 rows with value<>4).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Here is overall idea. Sample data:

create table blah(id int, username varchar(10), value int, week int)

insert into blah values(1 , 'bill' , 0, 1)
insert into blah values(2 , 'bill' , 1, 1)
insert into blah values(3 , 'bill' , 0, 1)
insert into blah values(4 , 'bill' , 1, 1)
insert into blah values(1 , 'craig', 1, 1)
insert into blah values(2 , 'craig', 0, 1)
insert into blah values(3 , 'craig', 1, 1)
insert into blah values(4 , 'craig', 0, 1)
insert into blah values(5 , 'craig', 1, 1)
insert into blah values(6 , 'craig', 1, 1)
insert into blah values(7 , 'craig', 0, 1)
insert into blah values(8 , 'craig', 1, 1)
insert into blah values(9 , 'craig', 0, 1)
insert into blah values(1 , 'john' , 0, 1)
insert into blah values(2 , 'john' , 1, 1)
insert into blah values(3 , 'john' , 0, 1)
insert into blah values(4 , 'john' , 1, 1)
insert into blah values(5 , 'john' , 1, 1)
insert into blah values(6 , 'john' , 0, 1)
insert into blah values(7 , 'john' , 1, 1)
insert into blah values(8 , 'john' , 1, 1)
insert into blah values(9 , 'john' , 0, 1)
insert into blah values(10, 'john' , 0, 1)

I still have no clues about week column; if it is part of grouping logic - (username, week) insead of (username) - then things become a little bit more complicated. Anyway:

declare @rc int; set @rc = -1
while @rc <> 0
begin
	update B
	set value = 4
	from blah B
	inner join
	(	select X.username, max(X.id) as foundid
		from blah X
		inner join
		(	select username, min(id) + convert(int, rand(checksum(newid()))*(max(id)-min(id) + 1)) as randid
			from blah
			where value <> 4
			group by username
			having count(*) > 5
		) Y on X.username=Y.username and X.id <= Y.randid
		where X.value <> 4
		group by X.username
	) Z on B.username=Z.username and B.id=Z.foundid

	set @rc = @@ROWCOUNT
end

Sample check; should return 0, 4 and 5 respectively (bill has 4 rows, craig 9 and john 10):

select username, sum(case when value=4 then 1 else 0 end), count(*)
from blah
group by username

select * from blah

drop table blah

Mathematically speaking, random distribution is not perfect - that would require ranking of some kind - but should be good enough.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

Sorry AA, it worked in 4 seconds this time but it did not update the value to 4 anywhere?

von:
Everyone always gets the same amount of IDs. Sometimes it is 16 other times it is 5 etc...

Any ideas as to why this does not work?

 

[aspnet98]

von:
your error says Line 21: Incorrect syntax near '@@ROWCOUNT'.

 

[aspnet98]

i am sorry von, i missed the END statement.

And also ignore my other post to you I messed up. I think your code worked and it is unreal. Both of you are genius. I really mean that. I did mess up because this code is sick and is very confusing to me. It will take me months to learn these steps. Stars to both for the help.

Let me doble check my 1500 records and I will post back with an update.

Von's code seemed to work very fast where AA's never executed fully?

For now I will stick with vons.

I will need to make three parameters for this which i do not know how to.

I need to make @username and @week and their is another field in the table i did not mention called IDGroup.

I need to pass parameters from an asp page i developed but I need help working those into the code von provided. can you help me add those in? like if i executed the sp from access project it would prompt me with those three parameters i mentioned.

Please let me know.

THANKS SO MUCH FOR YOUR HELP!

 

[amrita418]

I would use vongrunt's code too because it does not update one row at a time like mine (hence will be much quicker).

But I am still not sure why you are getting an error running the code that I posted. Works fine for me.

Regarding adding 2 more parameters can you be more clear on what is the expected output (also provide some sample input) for understanding the requirement better.

Regards,
AA

 

[aspnet98]

Sure, simply add one more column:

id username value week IDGroup
1 john 0 1 1000
2 john 1 1 1000
3 john 0 1 1000
4 john 1 1 1000
5 john 1 1 1000
6 john 0 1 1000
7 john 1 1 1000
8 john 1 1 1000
9 john 0 1 1000
1 craig 1 1 1000
2 craig 0 1 1000
3 craig 1 1 1000
4 craig 0 1 1000
5 craig 1 1 1000
6 craig 1 1 1000
7 craig 0 1 1000
8 craig 1 1 1000
9 craig 0 1 1000

I need to add parameters for the Week Column, Username, and IDGroup.

If I would try to run the von SP, it would prompt me for those three parameters and the code can stay the same it works perfectly.

 

[vongrunt]

But I am still not sure why you are getting an error running the code that I posted. Works fine for me.

Take a look at this:

set @v_id = ceiling(8 * rand(checksum(newid())) + 1)

Practically this generates random numbers in range [2, 9] because RAND() very rarely generates pure 0.000. Plus this interval is hard-coded; if one username has > 9 rows things can go wrong. For example, loop may run indefinitely if one username has 14 (9+5) or more rows and random updates fill rows 2-9 with value=4.

Sure, simply add one more column:

This doesn't explain the purpose of these parameters. Do you want to run query only over rows for which week and IDGroup equal specified parameters?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

Sorry von, damn internet was down for 2 days....
Forget the parameters for now, you said it would fail if one user had more than 9? most of my users will have 16 ids each? does that pose a problem?

 

[amrita418]

You do not have to worry about that since you are using vongrunt's code. He was explaining what could be the possible issues with the code that I posted.

I had originally assumed that all usernames have only 9 records and the id fields have values from 1-9 only, hence I was checking only for that. As it turns out there are other ids so the explanation for my code hanging.

Hope this clears up the misunderstanding.

Regards,
AA

 

[aspnet98]

Thanks, it does. I was wondering if you could help me with one other problem I am having, the data is in a similar view.

This is a tough SQL crash course for me, since I develop plain ASP pages...

I will start a new thread so I follow the TT rules.

Hope to see you there, it will be in this forum.