The Highest Score in a Class

Mrs. Jones is an admin in an elementry school called ‘SD Suka Tampil’. At the end of the school every six months she got to give report to the head master about students with the best score in every class. She has a table. Just a very simple table called StudentScore.

The structure is like

Name	Class	Score
Mina	1a	99
Arni	1b	98
Cukiyem	1a	55
Alfaredo	1c	78
Gilda	….

Aa.. just head to the query then to make this shorter.

CREATE TABLE #StudentScore (Name varchar(20), Class Char(2), Score int)

insert into #StudentScore (Name, Class, Score)
select 'Zee', '1a', 99 UNION ALL
select 'Arni', '1b', 98 UNION ALL
select 'Cukiyem', '1a', 55 UNION ALL
select 'Alfaredo', '1c', 78 UNION ALL
select 'Gilda', '1b', 88 UNION ALL
select 'Moore', '1a', 87 UNION ALL
select 'RiyanT', '1a', 90 UNION ALL
select 'Melissa', '1b', 98 UNION ALL
select 'Ninoood', '1c', 95

select * from #StudentScore

Name	Class	Score
Zee	1a	99
Arni	1b	98
Cukiyem	1a	55
Alfaredo	1c	78
Gilda	1b	88
Moore	1a	87
RiyanT	1a	90
Melissa	1b	98
Ninoood	1c	95

Ahaha that easy just use max

select Name, class, MAX(score) as 'The Highest'
from #StudentScore
group by Name, Class

Name	class	The Highest
Cukiyem	1a	55
Moore	1a	87
RiyanT	1a	90
Zee	1a	99
Arni	1b	98
Gilda	1b	88
Melissa	1b	98
Alfaredo	1c	78
Ninoood	1c	95

Why why why it don’t produce the right result? hehe cause you group it by the name and class. Hmm…
Let’s Find the highest first in every Class

select Class,  MAX(score) AS Score
from #StudentScore
group by Class

Class	Score
1a	99
1b	98
1c	95

Ahaaaa….

SELECT A.name, A.Class, A.Score as 'The Highest'
from #StudentScore A
JOIN (select Class,  MAX(score) AS Score
 from #StudentScore
 group by Class
 ) B ON A.Class = B.Class AND A.Score = B.Score

name	Class	The Highest
Ninoood	1c	95
Arni	1b	98
Melissa	1b	98
Zee	1a	99

Or use CTE

;WITH CTE AS (
SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;

Name	Class	Score
Zee	1a	99
Arni	1b	98
Ninoood	1c	95

wait why we can see melissa in class 1b?  please see below

SELECT Name, Class, Score,
ROW_NUMBER() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore

Name	Class	Score	RN
Zee	1a	99	1
RiyanT	1a	90	2
Moore	1a	87	3
Cukiyem	1a	55	4
Arni	1b	98	1
Melissa	1b	98	2
Gilda	1b	88	3
Ninoood	1c	95	1
Alfaredo	1c	78	2

why melissa is grade as number 2 in class 1b? because row number sort the score based on score
and when they found two same score it’ll sort again by name. And A comes first.
so how to have this with CTE. Use Dense Rank!

SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore

Name	Class	Score	RN
Zee	1a	99	1
RiyanT	1a	90	2
Moore	1a	87	3
Cukiyem	1a	55	4
Arni	1b	98	1
Melissa	1b	98	1
Gilda	1b	88	2
Ninoood	1c	95	1
Alfaredo	1c	78	2

Now we have Melissa and Arni in the same position
tadaaaa.

;WITH CTE AS (
SELECT Name, Class, Score,
Dense_rank() OVER (PARTITION BY Class Order by Score DESC) AS RN
FROM #StudentScore
)SELECT Name, Class, Score FROM CTE
 WHERE RN = 1;

I present to You…

Report For Miss Jones..

Name	Class	Score
Zee	1a	99
Arni	1b	98
Melissa	1b	98
Ninoood	1c	95

Advertisement