SQL server 2008, select students with highest grades from each class

Question

I have a db that looks like this

+--------+-------+-------+
| ID     | Class | grade |
+--------+-------+-------+
| 123    |   A   | 100   |
| 2      |   B   | 84    |
| 357    |   A   | 46    |
| 43     |   B   | 12    |
| 55677  |   B   | 78    |
| 63432  |   A   | 63    |
+--------+-------+-------+

obviously, I am dealing with much bigger db. more students, and more classes wha I want to achieve is select two students with the highest grades from each class.

So, it should show:

+-------+-------+-------+
| ID    | Class | Grade |
+-------+-------+-------+
| 123   | A     | 100   |
| 63432 | A     | 63    |
| 2     | B     | 84    |
| 55677 | B     | 78    |
+-------+-------+-------+

How do I achieve this thank you!

I tried this, but it gives me all rows

select id, class, max(grade)
from school
group by id, class

++++

There is another thing that I'd like to do with this db. How do I go about pulling top 10% students from each class? After I added a rank attribute, I tried using 'having' after group by like this:

rank < count(distinct ID) * 0.05

Thank you!

What if there's a tie in second place? I.e. add (321, B, 78) to the sample table data. — jarlh, Nov 08 '16 at 08:19

Mudassir Hasan · Accepted Answer · 2016-11-08T08:24:08.180

2

Use ROW_NUMBER() function with partition by clause.

;WITH CTE AS (

SELECT id, Class, Grade,
       ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Grade DESC) as rnk
FROM tableName
)

SELECT id, Class, Grade
FROM CTE
WHERE rnk <=2

edited Nov 08 '16 at 08:24

answered Nov 08 '16 at 07:52

Mudassir Hasan

28,083
20
99
133

`ROW_NUMBER` will not generate same rank for students with same grade. – Ullas Nov 08 '16 at 08:15
Do you know a way to pull top 10% students from each class? thank you! – Nayana Nov 09 '16 at 04:46
http://stackoverflow.com/questions/7579916/how-do-i-select-top-5-percent-from-each-group – Mudassir Hasan Nov 09 '16 at 04:49

score 1 · Answer 2 · answered Nov 08 '16 at 08:03

1

for this you need to use group by and having:

    select id,Class,Grade 
    from Student e 
    where Grade=(select max(Grade) 
    from Student w 
    group by w.Class having e.class=w.class)

answered Nov 08 '16 at 08:03

SURJEET SINGH Bisht

881
10
16

Ullas · Answer 3 · 2016-12-05T20:08:12.330

0

I think, DENSE_RANK is better here than ROW_NUMBER.

Query

;with cte as(
    select [rank] = dense_rank() over(
        partition by [Class]
        order by [grade] desc
    ), *
    from [your_table_name]
)
select * from cte
where [rank] <= 2;

edited Dec 05 '16 at 20:08

answered Nov 08 '16 at 08:00

Ullas

11,450
4
33
50

how did you line the database the way you did it? I always used a lot of spaces to line the numbers. How did you do + -- | this stuff? It looks a lot better, thank you for editing my question . – Nayana Nov 08 '16 at 09:03
Do you know a way to pull top 10% students from each class? thank you! – Nayana Nov 09 '16 at 04:46

score 0 · Answer 4 · answered Nov 08 '16 at 08:03

0

try this:

WITH result AS (

SELECT Id, Class, Grade,
       dense_rank() OVER(PARTITION BY Class ORDER BY Grade DESC) as DenseRank
FROM tableName
)

SELECT Id, Class, Grade
FROM result
WHERE DenseRank <=2

more than one student with same highest grade will also be considered.

answered Nov 08 '16 at 08:03

James

729
6
10

Do you know a way to pull top 10% students from each class? thank you! – Nayana Nov 09 '16 at 04:46

SQL server 2008, select students with highest grades from each class

4 Answers4