Allows say I've got a table with three posts, key, value and priority having a unique key on (key, priority).

I wish to choose all key/value pairs where priority for your secret is greater than every other priority for the similar key.

For instance:

key | value   | priority
----------------------
'a' | 'Alice' |   5 
'a' | 'Aaron' |   10
'b' | 'Bob'   |   1

Should return:

key | value
-------------
'a' | 'Aaron'
'b' | 'Bob'

What's a competent way with words this type of query?

select [key],
       value
from (select [key],
             value,
             row_number() over(partition by [key] 
                               order by priority desc) as rn
      from YourTable) as T
where rn = 1
    SELECT a.`key`,
           a.`value`
      FROM tbl a
INNER JOIN (SELECT MAX(priority) priority,
                   `key`
              FROM tbl
          GROUP BY `key`) b ON a.`key` = b.`key`
                           AND a.priority = b.priority

This question will act as lengthy as each key has only one priority with greatest value

Make use of this SQL:

SELECT key, value 
FROM T1 as t1
JOIN 
(SELECT key, MAX(priority) AS priority 
 FROM T1 as t2 
 GROUP BY key) as t3 
ON t1.key = t3.key AND t3.priority = t1.priority