Voting Table Dupes

anyone got a idea

[bateman_ap]

Just wanted a general idea to help with a problem. I am writing some new systems and what i need to do is clean up some voting values.

What I want to do is go through a database table and trim down some multiple votes. ie those that occur from the same ip, for the same widget in a certain timeframe, say a hour.

At the moment in the table it is stored as:
fld_ipaddress
fld_widget_uid
fld_vote
fld_datetime
fld_allow (defaults to 1)

So for example there might be multiple votes a few seconds apart for one widget uid, I want to write something that just updates the fld_allow row to 1

Do I have to write a Loop that takes each vote, selects everything with the same UID, ipaddress and has a datetime between say a hour and then write a UPDATE query to set the returned records fld_allow to 0?

If I do this would it jsut kill the server, at the moment there are about 250k votes.

[RossWal]

Probably more efficient to join the table to itself. Something like this:

select *
from mytable a, mytable b
where a.ip_addr = b.ip_addr
and a.timestamp > b.timestamp - 1 hour
and a.timestamp < b.timestamp + 1 hour
and a.timestamp <> b.timestamp

You may be able to wrap this in an update statement or insert its results into a temp table for use in generating updates.

[tomasz]

loop would be expensive, I would use group by datediff(h,getdate(),flddatetime)