why the cross apply is giving all the rows and not top N

Question

I have a table

name    score
samar   12
pradeep 30
garry   90

I want to get the top 2 scores from the above table. This can be done by row_number() function. But it should also work with cross apply. But my below mentioned code is giving all the rows instead of top 2 score.

select abc.score
from #b as a
cross apply (
    select top 2 score
    from #b as 
    where b.name = a.name
    order by b.score desc
) as abc

Can you please tell me what's wrong in the above code.

Answer 1

Please check if below query works in your case.

create table #b
(name varchar(30), score int);


insert into #b (name, score)
values ('mohit',12),
('pradeep',30),
('garry',42)


SELECT name,
       score,
       ROW_NUMBER() OVER(ORDER BY Score) RN
INTO #c
FROM #b

SELECT name,
       score,
       CONVERT(NUMERIC,score)/
       (SELECT SUM(score)
        FROM #c
        WHERE RN <= 2) Percentage
FROM #c
WHERE RN <= 2