# Choose greatest priority row

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
``````