Nayana Nayana - 24 days ago 7
SQL Question

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

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!

Answer

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
Comments