Thanks Paul , this gave me the expected result. But i need to test under different situations.
Which one will be better ? a cross apply or with cte ? i donno what cross apply does.
I terms of performance , that will depend entirely on your real data.
Maybe returning the top 999 of 1000 rows of 1 group will be outweighed by returning the top 5 of 100000 rows in another. The only real way to be sure is to try it and see what the query plan says. There are hundreds of things that can make 1 query that returns the same data as another perform better or worse.