CODEWITHSUNDEEP
mysqlsql-order-by
Vanawy
Vanawy

Reputation: 113

Order by max value in group

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

Upvotes: 2

Views: 300

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • 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

Upvotes: 2

Related Questions