There is a student(id_student, stname, averagemark, id_group) table student(id_student, stname, averagemark, id_group) . The task is to get one student from the group with the highest grade. Here is my option:

 SELECT s1.stname, (SELECT max(averagemark) FROM student AS s2 WHERE s2.id_group = s1.id_group) AS averagemark, s1.id_group FROM student AS s1 ORDER BY id_group;

But he displays all the students with the highest mark (that is, if the table in the group 101 has five students the highest mark, the result will show all five. But one is needed)

Thank you in advance!

  • select top 1 ? - andreycha
  • Of these 5 - need a student selected by some particular principle? Or just one any student? If any of these five - then add TOP 1 - MANKK
  • @andreycha doesn't work like that. If we add top 1 to the main select, then the result will be only one row - for the entire table - Ekaterina Bogush

1 answer 1

We divide the table into partitions using over and partition by , sorting the contents of partitions by descending ratings, so Thus, in each partition, we obtain the maximum estimate in the first line. Then we select all first entries from partitions ( where num = 1 ).

 select stname, averagemark, id_group from ( select *, row_number() over (partition by id_group order by averagemark desc) num from students ) parts where num = 1