update top 10 of every groupid

[jacob94]

How can I update the top 2 records of every group in a table to the number 1.

GroupID Rec
1 1
1 1
1
2 1
2 1
2

etc.

Seems simple, but i can't seem to write it. i tried all sorts of subqueries but i doubt that is the route to go?

any help would be greatly appreciated.

 

[rac2]

Suppose the rows in the table have a unique id, whatsitz_id, then possibly

UPDATE MyTable SET
   rec = 1 
WHERE whatsitz_id IN (
                  SELECT TOP 2 whatsitz_id
                  FROM MyTable
                  WHERE GroupID = a.GroupID
                  ORDER BY whatsitz_id
                 )
FROM MyTable a

 

[jacob94]

rac2: My table has over 1 million records and this update never excutes all the way through and crushes my server. Any other solutions, this seems like it would be simple, but it just is not...

Thanks for helping me...

 

[rac2]

Is the primary key indexed?

Can you add a WHERE clause and limit the update to a range of ids, small enough to not crush your server?

 

[jacob94]

yes

when i add where it slows it down more

 

[rac2]

An index on GroupID column may help.