CODEWITHSUNDEEP
mysql
Zolka
Zolka

Reputation: 49

MySQL query: select records with highest value in group

I have a MySQL table like this.

| season_id | round_1 | names  | score_round_1
| 5         | 10      | John1  | 5  
| 5         | 10      | John2  | 3
| 5         | 11      | John3  | 2
| 5         | 11      | John4  | 5

I want to select the records with highest score_round_1 in each round_1(10,11) group .

In this case the first and last rows would be selected.

I tried using the GROUP BY round_1 but that only returns the first row from the two.

Any advice?

Zolka

Upvotes: 1

Views: 5036

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

This is simple

select max(score_round_1),
       name
from score
group by round_1

Upvotes: 1

Andreas Wong
Andreas Wong

Reputation: 60516

Use aggregate function MAX

SELECT names, MAX(score_round_1) GROUP BY round_1

Upvotes: 0

elo
elo

Reputation: 615

SELECT *
FROM table p1
WHERE score_round_1 = (
SELECT MAX( p2.score_round_1 )
FROM table p2
WHERE p1.round_1  = p2.round_1  ) ANDround_1 !=0

Upvotes: 0

Related Questions