SQL : Records with highest value of a column-CodePudding

I have a list of marks like in the table below

would like to get a result like the below. The result should give the records of student with maximum mark and the Record ID

CodePudding user response：

schema of table with rows values

create table notes(RecordID int,Students varchar(255), Marks int);
insert into notes values (1, 'Vladimer', 78),(2, 'Vladimer', 40),(3, 'Vladimer', 64),(4, 'Paul', 99),(5, 'Paul', 86),(6, 'Paul', 65),(7, 'Mathew', 76),(8, 'Mathew', 88),(9, 'Mathew', 65);

You could simply use sub-query:

SELECT *
FROM notes n
WHERE Marks = (SELECT MAX(Marks) FROM notes WHERE Students = n.Students)

other way using top:

select * 
from notes n 
where RecordID = (select top 1 RecordID 
                    from notes 
                    where Students  = n.Students  
                    order by Marks DESC);

Another approach with

CodePudding user response：

Finally, got the answer.

SELECT RecordID, 
           Students, 
           Marks, 
           RANK() OVER(PARTITION BY Students ORDER BY Marks DESC) Rank
    FROM Table
    ORDER BY Students, 
             Rank;

CodePudding user response：

Your question is not clear.

If you want the top mark per student:

select top(1) row with ties *
from marks
order by row_number() over (partition by student order by mark desc);

If you wanted the top n results, e.g. the top 3, (but you don't, as we can clearly see from the expected result you are showing):

select top(3) row with ties
from marks
order by mark desc;

If you want the top n students, e.g. the top 3:

select top(3) row with ties
from ( <first query> ) top_marks
order by mark desc;

There exist other options of course. In order to get the top mark per student, you could use NOT EXISTS for instance to make sure there exists no row with a better mark for the student:

select *
from marks
where not exists
(
  select null
  from marks better
  where better.student = marks.student
  and better.mark > marks.mark
);