Please see this thread http://forums.asp.net/t/1362385.aspx with very interesting solution.

select [id],[name]
from test as A1
where (select count(*) from test as A2 where A2.[id]=A1.[id] and A2.name<=A1.name )<=2
order by [id]

>Hi all,
>
>I have a mytable with 2 fields:
>
>team score
>AAAA 10
>AAAA 12
>AAAA 14
>AAAA 16
>AAAA 18
>AAAA 20
>AAAA 22
>
>BBBB 11
>BBBB 22
>BBBB 33
>BBBB 44
>BBBB 55
>BBBB 66
>
>CCCC 22
>CCCC 77
>CCCC 88
>CCCC 99
>
>DDDD 10
>DDDD 12
>
>to have in mynewtable AT LEAST the top 3 in each team if that team has more
>than 3 records.
>
>team score
>AAAA 22
>AAAA 20
>AAAA 18
>
>
>BBBB 66
>BBBB 55
>BBBB 44
>
>CCCC 99
>CCCC 88
>CCCC 77
>
>DDDD 12
>DDDD 10
>
>I try this SQL statement but not work.
>
>

>
>SELECT TOP 3 TEAM,SCORE FROM MYTABLE WHERE TEAM IN ( SELECT TEAM,SCORE FROM MYTABLE ORDER BY TEAM, SCORE DESC;
>
>

>
>Please assist to correct my sql.
>
>TIA