Search code examples
phpmysqlsubquerysql-order-bywindow-functions

How do I order groups by each group's highest value

I have data:

query url score  
a www.google.com 3  
a www.facebook.com 2  
a www.google.com 1

I want to 'group' entries by their domain, and then order (desc) the domain groups by each group's highest score so I get:

query url score  
a www.google.com 3  
a www.google.com 1
a www.facebook.com 2

Trying: select * from table order by score desc, url asc doesnt work. It gives (no apparent change):

query url score  
a www.google.com 3  
a www.facebook.com 2  
a www.google.com 1
Solution

  • You can use window functions - if you are running MySQL 8.0:

    select *
from mytable
order by max(score) over(partition by url) desc, score desc

    In earlier versions, one option uses a subquery:

    select *
from mytable t
order by 
    (select max(score) from mytable t1 where t1.url = t.url) desc,
    score desc