SQL Help - Review Required, Is my solution sound?

[Vis1/0N]

ID	SCHOOL	Grade	Student	Status	TUT	sYear	Active
2	NH	ONE	DAN	1		2017	TRUE
3	NH	ONE	JON	1	12017	2017	TRUE
4	NH	ONE	MAY	1		2017	TRUE
5	SC	TWO	PAM	4	22010	2016	FALSE
6	SC	TWO	PAT	2	22010	2017	TRUE
7	SC	TWO	FAF	5	22010	2010	FALSE
8	SC	TWO	RON	3		2017	TRUE
9	SC	THREE	SAM	6		2017	TRUE
10	SC	THREE	ROB	2		2017	TRUE
11	SC	THREE	ANN	2	32017	2017	TRUE
12	SC	FOUR	SUE	6		2017	TRUE
13	SC	FIVE	LEE	2		2017	TRUE
14	SC	SIX	BEN	6		2017	TRUE
15	SC	SIX	ANN	2	62017	2017	TRUE
16	NH	SEVEN	AMY	6		2017	TRUE
17	NH	SEVEN	EDD	2	72017	2017	TRUE
18	NH	SEVEN	IAN	2	72017	2017	TRUE
19	SC	SEVEN	IDA	2	72016	2017	TRUE
20	SC	SEVEN	IVY	6		2016	FALSE
21	SC	SEVEN	KIM	5	72016	2017	TRUE

Expected Output

NH	ONE	1	12017
SC	TWO	3	22010
SC	THREE	6	32017
SC	FOUR	6	
SC	FIVE	2	
SC	SIX	6	62017
NH	SEVEN	6	72017
SC	SEVEN	5	72016

ACCESS SQL that gives the correct output :

Select  SISSet.SCHOOL, SISSet.Grade,StatusSet.MaxOfStatus as Status, SISSet.SIS as Tut
from
(
SELECT DISTRICT.SCHOOL, DISTRICT.Grade, max(Tut) as SIS
FROM DISTRICT
WHERE (Active=True)
GROUP BY DISTRICT.SCHOOL, DISTRICT.Grade
) as SISSet

INNER JOIN
(
SELECT DISTRICT.SCHOOL, DISTRICT.Grade, Max(DISTRICT.Status) AS MaxOfStatus
FROM DISTRICT
WHERE (Active=True)
GROUP BY DISTRICT.SCHOOL, DISTRICT.Grade
) as StatusSet
on ((StatusSet.SCHOOL = SISSet.SCHOOL) and (StatusSet.Grade = SISSet.Grade) )

order by SISSet.SCHOOL, SISSet.Grade;

The table is abstract data for a complete different industry, imagine the rule is to show the grades for each school, with the best score and the tutors code if one was active in the grade (even if not assigned to the best student - therefore the JOIN).

I would appreciate any suggestions. Is my solution sound? Is there something more elegant?

 

[Messugga]

Why not have everything as one SELECT statement? I don't see the point of creating two datasets and joining them?

Don't have your DB so can't test my syntax, but this should give the same output, if I'm not mistaken:

SELECT DISTRICT.SCHOOL, DISTRICT.Grade, max(Tut) as SIS, Max(DISTRICT.Status) AS MaxOfStatus
FROM DISTRICT
WHERE (Active)
GROUP BY DISTRICT.SCHOOL, DISTRICT.Grade
order by SISSet.SCHOOL, SISSet.Grade;


Edit - Oh right, now I understand the point of the JOIN.

 

[Hamster]

Homework?

 

[Vis1/0N]

Homework?

It will be this weekend ... unpaid Easter holiday overtime. I have obfuscated the customer data in my example and simplified. The system spec is constantly being revised which is possibly why the customer bypassed their internal team and outsourced. It went from basic to being laden with edge cases.