How to use the highest values ​​for a certain criterion to sort the list?

advertisements

For the following data in a table, I want to get the highest balance for each person and then sort the result in descending order of that highest balance:

|---------id--------|--------name--------|--------balance--------

          1         |        Tom         |      10
          2         |        Bob         |      20
          3         |        Ace         |      60
          4         |        Tom         |      45
          5         |        Ace         |      35
          6         |        Tom         |      75
          7         |        Dan         |      15
          8         |        Dan         |      95
          9         |        Tom         |      40
          10        |        Dan         |      30
          11        |        Bob         |      65
          12        |        Dan         |      55
          13        |        Bob         |      50

the expected result is (For descending order):

|--------id---------|--------name--------|--------balance--------

          8         |        Dan         |      95
          6         |        Tom         |      75
          11        |        Bob         |      65
          3         |        Ace         |      60

What would be the mySQL query for this?


maybe like this:

select pid, name, max(balance) as highest_balance from my_table
group by pid, name
order by max(balance) desc;