select the data of all the students scoring highest marks in each subject group by city

I am using mysql version 8.0.23

the given table is:-

+-----+--------+-----------+-------+-----------|
| id  | name   | subject   | marks | city      |
+-----+--------+-----------+-------+-----------|
| 101 | ravi   | maths     |    70 | Mumbai    |
| 103 | Viaan  | english   |    26 | Bangalore |
| 104 | varun  | chemistry |    95 | delhi     |
| 105 | rishab | biology   |    69 | delhi     |
|  108| Mihika | maths     |    78 | Kolkata   |
|  110| Ishaan | english   |    39 | Bangalore |
+-----+--------+-----------+-------+-----------|

and I want to get the complete data of the students having highest marks in each subject from each city

select subject, city, max(marks) as highest
from students
group by subject, city
order by subject, city;

—

select subject
     , city
     , max(marks) as highest 
  from students 
 group 
    by subject
     , city 
 order 
    by subject
     , city;

+-----------+-----------+---------+
| subject   | city      | highest |
+-----------+-----------+---------+
| biology   | Bangalore |      87 |
| biology   | Chennai   |      58 |
| biology   | delhi     |      82 |
| biology   | Jaipur    |      52 |
| biology   | Kolkata   |      92 |
| biology   | Lucknow   |      98 |
| chemistry | Bangalore |      84 |
| chemistry | Chennai   |      64 |
| chemistry | delhi     |      95 |
| chemistry | Jaipur    |      83 |
| chemistry | Kolkata   |      45 |
| chemistry | Lucknow   |     100 |
| chemistry | Mumbai    |      87 |

I had used this but still I am not able to see name and id of that student

Answer

here is one way using window functions:

select * from 
   ( 
    select * , rank() over (partition by subject, city order by mark desc) rn
    from yourtable
   ) t
where rn = 1