CODEWITHSUNDEEP
sqlpostgresqlsqlalchemy
Bhakta Raghavan
Bhakta Raghavan

Reputation: 724

Sql query to get distinct rows based on a criteria

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.

Upvotes: 0

Views: 35

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

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

Upvotes: 1

Related Questions