basic sql group by with percentage

Question

I have an issue and NO it is not homework, it's just a programmer who has been away from SQL for a long time having to solve a problem.

I have the following table:

create table students(
    studentid int identity(1,1), 
    [name] varchar(200), 
    [group] varchar(10), 
    grade numeric(9,2) 
)
go

The group is something arbitrary, assume it's the following "Group A", "Group B"... and so on.

The grade is on a scale of 0 - 100.

If there are 5 students in each group with grades randomly assigned, what is the best approach to getting the top 3 students (the top 80%) based on their grade?

To be more concrete if I had the following:

Ronald, Group A, 84.5
George H, Group A, 82.3
Bill, Group A, 92.0
George W, Group A, 45.5
Barack, Group A, 85.0

I'd get back Ronald, Bill, and Barack. I'd also need to do this over other groups.

Answer 1

Use a TOP (n) PERCENT clause:

SELECT TOP (60) PERCENT * FROM students ORDER BY grade DESC

Answer 2

Take a look at first part of More with SQL Server 2005 : Top n Per Group, Paging, and Common Table Expressions. To get top 3 students in each group you can use:

SELECT  d.studentid,
        d.name,
        d.group,
        d.grade

FROM   (SELECT  s.studentid,
                s.name, 
                s.group, 
                s.grade
                ROW_NUMBER = ROW_NUMBER() OVER (
                    PARTITION BY s.group
                    ORDER BY s.grade DESC)
        FROM    students s
        ) d

WHERE   d.ROW_NUMBER <= 3