Order by max value in group

Question

i have this

Name   | Team     | Value
-------|----------|--------
A      | RED      |1
B      | BLUE     |2
C      | RED      |3
D      | BLUE     |4
E      | BLUE     |5

i need this: Team with higher max value showed first

Name   | Team     | Value
-------|----------|--------
B      | BLUE     |2
D      | BLUE     |4
E      | BLUE     |5
A      | RED      |1
C      | RED      |3

Possible duplicate of [SQL multiple column ordering](https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) — apetranzilla, Sep 28 '18 at 18:55

score 2 · Accepted Answer · answered Sep 28 '18 at 16:36

First, find "Rank" (maximum Value) for each Team group in a Derived Table t2.
Join this derived table with your table based using Team.
Now, sort the results by "Rank" in descending order, and Name in Ascending order. So that the rows of the Team with highest maximum value come first, sorted by their Name within themselves.

You can try the following query (replace your_table with your actual table name):

SELECT t1.Name, 
       t1.Team, 
       t1.Value 
FROM your_table AS t1 
JOIN 
(
  SELECT t2.Team, 
         MAX(t2.Value) AS rank 
  FROM your_table AS t2 
  GROUP BY t2.Team 
) AS t2 ON t2.Team = t1.Team 
ORDER BY t2.rank DESC, t1.Name ASC

Order by max value in group

1 Answers1