Select Max from multiple rows

Transact-SQL
1

Hi - first of all, I'm new to SQL, so may be asking an obvious question :relaxed:

I need to select the max LEVEL for each SUBJECT - basically a pupil could have taken a test more than once in different subjects, and I need to get the highest level achieved for each subject (I've attached an image)

I've tried using

select *
from examresults
where level = (select max(Level) from examresults as examresults1)

But that just returns the highest level out of the whole lot, whereas I need the highest level for EACH subject by EACH pupil. If that's even possible??

Thanks in advance.

Using Microsoft SSMS 2014

2

that statement will tell you to use MAX ( LEVEL ) ..... GROUP BY SUBJECT.

Give it a try

1 Like
3 
-- *** Test Data in Consumable Format ***
-- Please provide in future
CREATE TABLE #t
(
	PupilID int NOT NULL
	,SubjectID int NOT NULL
	,Level int NOT NULL
);
INSERT INTO #t
VALUES (3110, 2, 145)
	,(3110, 2, 210)
	,(3110, 3, 170)
	,(3110, 3, 181)
	,(3110, 14, 26)
	,(3110, 14, 31);
-- *** End Test Data ***

SELECT PupilID, SubjectID, MAX(Level) AS Level
FROM #t
GROUP BY PupilID, SubjectID;
1 Like
4

It worked, thank you very much

5

Fantastic - thanks for the breakdown, it worked perfectly. I will also set out future posts in the way you demonstrated

Thanks a lot

6

Another way for ref.

CREATE TABLE #tblEXAMS
(
PID int not null IDENTITY (1,1) PRIMARY KEY,
PuplilID int not null,
SubjectID int  not null,
Level int not null,
)


INSERT INTO #tblEXAMS VALUES 
(3110, 2, 145),
(3110, 2, 210),
(3110, 3, 170),
(3110, 3, 181),
(3110, 14, 26),
(3110, 14, 31)

WITH cte
AS
(
SELECT  [PuplilID],
	[SubjectID],
	[Level],
	ROW_NUMBER()OVER(PARTITION BY SubjectID ORDER BY LEVEL DESC) rn

 FROM #tblEXAMS
 )
 SELECT [PuplilID],
	[SubjectID],
	[Level] 
 FROM cte 
 WHERE rn = 1
1 Like
7

That's useful too, thanks SZ1

8

You're missing the ; before the WITH cte.