There is a table(named Students) containing columns:

Name, Subject, Mark

It is required to return the name and the Subject of the best students. Student is considered best in the subject if he/her has the largest amount of the best marks.

So, if there are entries in the table:

('John', 'Math', 10),
('John', 'Math', 10),
('John', 'Math', 11),
('Mia', 'Math', 10),
('Mia', 'Math', 11);
('Bob', 'Science', 12),
('Bob', 'Science', 11),
('Ross', 'Science', 11),
('Ross', 'Science', 12),
('Ross', 'Science', 12)

The query should return

John Math
Ross Science

Because John has two tens and one eleven. Mia has one ten less.

I understand that I need to group entries by Subject, Name and Mark and count the amount of same marks. I tried the following query:

SELECT NAME, SUBJECT, MARK, COUNT(*) AS COUNT
FROM STUDENTS
GROUP BY SUBJECT, NAME, MARK

It returns:

John    Math    10  2
John    Math    11  1
Mia Math    10  1
Mia Math    11  1
Bob Science 12  1
Bob Science 11  1
Ross    Science 11  1
Ross    Science 12  2

I have an idea that I need to discard the entries where students have the same amount of particular marks. Here they are Mia and John's elevens. They are of the same amount. So table will look like this:

John    Math    10  2
Mia     Math    10  1
Bob     Science 12  1
Ross    Science 12  2

And now I have to pick the student with larger amount of marks. But the problem is I have no idea how to do this.

I do not ask for the full solution. I particularly ask for help regarding my idea, whether it is reasonable, and, if not, to suggest an alternative

CodePudding user response：

I counted the top marks per name and subject and then numbered by max(mark) desc and then count desc to get only the result with the most counts.

select name 
      ,subject 
  
from   (
       select name
             ,subject
             ,rank() over(partition by subject order by max(mark) desc, count(*) desc) as rnk
       from   t
       group by name, subject
       ) t
where  rnk = 1

Fiddle

CodePudding user response：

This sounds like homework so I will answer in the most general terms for you to investigate to completion.

Investigate the SQL language looking for topics dealing with:

1. aggregating data using different functions
2. ordering results
3. limiting the number of results returned

You will most likely have to use different functions and group by syntax in the end.