Let's say I have a MySQL table with the following rows:

The goal is to return a list of rows with unique key while giving priority to rows which have a branchId. The expected returned rows are:

I've tried using GROUP BY statements alongside ORDER BY but was not successful.

Note that I do not have access to the config server to tweak things like sql_mode.

CodePudding user response：

Thanks PM 77-1 for the comment. This question is indeed associated with "Top 1 per group" tasks.

The answer is to group by key with a MAX() on branchId

SELECT `key`, max(branchId) from `table` group by `key`;

CodePudding user response：

Here's a simple solution based on group by and order by.

select   max(id) as id
        ,`key`
        ,max(branchId) as branchId
       
from     t 
group by `key`
order by branchId  

Fiddle