0

I want to determine the SQL to get the toppers in each grade based on the score

scores:
+----+---------+--------+
| id | score   | grade  |
+----+---------+--------+
|  1 |  48     |  1     |
|  2 |  56     |  2     |
|  3 |  69     |  1     |
|  4 |  35     |  1     |
|  5 |  78     |  2     |
|  6 |  90     |  2     |
|  7 |  87     |  2     |
|  8 |  33     |  1     |
+----+---------+--------+

Expeted Result:
+----+---------+--------+
| id | score   | grade  |
+----+---------+--------+
|  3 |  69     |  1     |
|  6 |  90     |  2     |
+----+---------+--------+

What I am doing now is:

   allGrades = [1, 2]
   for <grade> in allGrades:
       select * from scores where grade=<grade> order by score desc limit 1;

Is there a more efficient way to do this in a single query?

I am trying to do this with sqlalchemty with postgres - but SQL solution would be good and I can convert it.

Bhakta Raghavan
  • 664
  • 6
  • 16
  • In PostgreSQL you can use the [`DISTINCT ON ... ORDER BY`](https://stackoverflow.com/questions/16914098/how-to-select-id-with-max-date-group-by-category-in-postgresql/16920077#16920077) method. It can easily be [translated to SQLAlchemy (pre 2.x)](https://stackoverflow.com/questions/57253307/what-is-the-correct-way-to-use-distinct-on-postgres-with-sqlalchemy/57254089#57254089). – Ilja Everilä Jul 12 '22 at 11:24
  • @IljaEverilä Thank you. This is perfect and works like a charm with sqlalchemy and postgres – Bhakta Raghavan Jul 12 '22 at 15:50

1 Answers1

1
select s.* 
from scores s
inner join (
select grade, max(score) as score 
from scores
group by grade ) sg on s.grade = sg.grade and s.score = sg.score;

DBFiddle demo

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39