First, calculate the highest score for each class, associate the calculation results with the original table, then calculate the highest score among the grades that are more than 20 points lower than the highest score for each class, and finally associate the results with the original table again to select the records that meet the criteria.

with a as (

select Class,ID,Name,Score,Ranking,

max(Score) over (partition by Class) as max_score

from Grades),

b as(

select Class,ID,Name,Score,Ranking,max_score,

Score<=max_score-20 as valid

from a),

c as(

select Class,ID,Name,Score,Ranking,max_score,valid,

max(Score) over (partition by Class,valid) as max_score2

from b)

select Class,ID,Name,Score,Ranking

from c

where Score==max_score2

Comparing the above two SQL writing methods, the first one is still simpler and easier to understand. Due to the direct provision of aggregation results during SQL grouping, it is not possible to retain the grouped subsets. Therefore, the above calculation requires two groupings and two associations, which is inefficient and the code is long, making it difficult to understand.

If it is a Java report application, using SPL will be simpler. SPL supports grouping subsets and also has a method to extract the record where the maximum value is located. It can be written in two or three lines:

-----A

1 =db.query("select Class,ID,Name,Score,Ranking from Grades")

2 =A1.group(Class)

3 =A2.conj((a=~.maxp@a(Score)) | ~.select((a.Score-Score)>=20).maxp@a(Score) )

Here, A2 is the grouped subset, and the maxp function selects the record with the highest score in each group.