SQL Server Select Top Within Subsets?

I have this data:

Create Table #Some ( ForeignId Int , SubValue UniqueIdentifier , WasRead Bit)
Insert Into #Some Values ( 1 , 'ADEE70A8-E804-4A0C-93AD-00CCCB3DA7DA' , 0 )
Insert Into #Some Values ( 1 , '024EDED5-744B-497A-BFB8-06A0C62DE0AE' , 0 )
Insert Into #Some Values ( 1 , 'C3F255F开发者_如何转开发9-3648-4D8D-8D84-0951BD2D0551' , 0 )
Insert Into #Some Values ( 1 , '25CE8E67-A263-409E-8E7F-1A814EF76524' , 0 )
Insert Into #Some Values ( 2 , '1B339D13-B580-4660-9642-0C0FA9C84D14' , 0 )
Insert Into #Some Values ( 2 , '579C5BD3-EE3A-4789-A57B-0FAD0F7227A2' , 0 )
Insert Into #Some Values ( 3 , '31D1E0F2-5F8A-451B-8114-14DB685E9F53' , 0 )
Insert Into #Some Values ( 3 , '52347A60-4156-401F-8570-15554DD905EF' , 0 )
Insert Into #Some Values ( 3 , '6ECD072E-BC43-4A4C-AACD-1A6C256DCFDE' , 0 )
Insert Into #Some Values ( 3 , '3264625D-94CE-4798-9E4B-2672EDE496BC' , 0 )

I need to select the first three rows for each ForeignId, update their WasPolled value to 1, and emit multiple result sets, but the total size for all sets cannot be more than 8:

set:

1 , 'ADEE70A8-E804-4A0C-93AD-00CCCB3DA7DA' 
1 , '024EDED5-744B-497A-BFB8-06A0C62DE0AE' 
1 , 'C3F255F9-3648-4D8D-8D84-0951BD2D0551' 

set:

2 , '1B339D13-B580-4660-9642-0C0FA9C84D14' 
2 , '579C5BD3-EE3A-4789-A57B-0FAD0F7227A2' 

set:

3 , '31D1E0F2-5F8A-451B-8114-14DB685E9F53' 
3 , '52347A60-4156-401F-8570-15554DD905EF' 
3 , '6ECD072E-BC43-4A4C-AACD-1A6C256DCFDE' 

---

with cte as
(
    select ForeignId, SubValue, row_number() over(partition by ForeignId order by SubValue) as RowNumber
    from #Some
    where WasRead = 0
)
update top (8) s
set WasRead = 1
from #Some s
    join cte on
        cte.ForeignId = s.ForeignId and cte.SubValue = s.SubValue
where cte.RowNumber <= 3;

I don't know if you have a primary key. If you do use it for the join. I assumed that ForeignId + SubValue is unique

---

This is how you'd update the first three rows - you need to provide a means of ordering the data:

WITH example AS (
  SELECT t.*, 
         ROW_NUMBER() OVER(PARTITION BY t.foreignid 
                               ORDER BY t.subvalue) AS rank
    FROM #some t)
UPDATE example
   SET wasread = 1
 WHERE rank BETWEEN 1 AND 3

"emit multiple result sets, but the total size for all sets cannot be more than 8:" - Huh?! What do you mean by "emit". Selection means a separate statement:

Non-CTE version:

  SELECT x.*
    FROM (SELECT t.*, 
                 ROW_NUMBER() OVER(PARTITION BY t.foreignid 
                                       ORDER BY t.subvalue) AS rank
            FROM #some t) x
   WHERE x.rank BETWEEN 1 AND 8

CTE version:

WITH example AS (
  SELECT t.*, 
         ROW_NUMBER() OVER(PARTITION BY t.foreignid 
                               ORDER BY t.subvalue) AS rank
    FROM #some t)
SELECT e.*
  FROM example e
 WHERE e.rank BETWEEN 1 AND 8

---

Use the Ranking Functions, particularly ROW_NUMBER to limit the number of results returned.