CODEWITHSUNDEEP
sqlsqlite
Greelings
Greelings

Reputation: 5434

Sqlite : ORDER BY the MAX value of each group, but returning all rows

I'm stuck with this one. I would like to order a table by the max value of each group. (but returning all rows).

As it is always easier with an example :

Team | Player | Score
---
red  | Bob  | 32  
red  | Jill | 34  
red  | Shawn| 42  
blue | Jake | 29  
blue | Paul | 26
blue | Mike | 35

The desired result set :

blue | Jake | 35  
blue | Paul | 35
blue | Mike | 35
red  | Bob  | 42   
red  | Jill | 42   
red  | Shawn| 42

Upvotes: 0

Views: 627

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a inner join on select table 

  select t.team, a.player, t.score
  from my_table 
  inner join (
      select team, max(score)
      from my_table 
      group by team
  ) t on t.team = a.team 
  order by t.team

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use a correlated subquery:

select t.team, t.player,
       (select max(t2.score) from t t2 where t2.team = t.team) as max_score
from t;

You can do something similar with a group by and join.

Upvotes: 2

Related Questions