Allows say I've got a table with three posts,
priority having a unique key on (
I wish to choose all
value pairs where
priority for your secret is greater than every other priority for the similar
key | value | priority ---------------------- 'a' | 'Alice' | 5 'a' | 'Aaron' | 10 'b' | 'Bob' | 1
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