Selecting one row from a group in SQL with multiple selectors

Question

(This question is specific to MySQL 5.6, which does not include CTEs)

Let's say I have a table like this (actual table is made from a subquery with several joins and is a fair bit more complex):

ID  NAME    POWER    ACCESS_LEVEL  DEPTH
1   Odin    poetry   1             0
1   Isis    song     2             1
2   Enki    water    1             0
2   Zeus    storms   2             2
2   Thor    hammer   2             3

I want to first group them up by ID (it's actually a double grouping by PRINCIPAL_TYPE, PRINCIPAL_ID if that matters), then select one row from each group, preferring the row with the highest ACCESS_LEVEL, and among rows with the same access, choosing the one with the lowest depth. No two rows will have the same (ID, DEPTH) so we don't need to worry beyond that point.

For example with the above table, we select:

ID  NAME    POWER    ACCESS_LEVEL  DEPTH
1   Isis    song     2             1
2   Zeus    storms   2             2

The groups are (Odin, Isis) and (Enki, Thor, Zeus). In the first group, we prefer Odin over Isis because Odin has a higher ACCESS_LEVEL. In the second group, we take Zeus because Zeus and Thor have higher ACCESS_LEVELs than Enki, and between those two, Zeus has the lower depth.

Worst case Ontario, I can do it at the application level, but doing it at the database level allows for using LIMIT and SORT BY to do paging, instead of fetching the whole result set.

Answer 1

Here is one method that uses a correlated subquery:

select t.*
from t
where (access_level, depth) = (select access_level, depth
                               from t t2
                               where t2.id = t.id
                               order by access_level desc, depth asc
                               limit 1
                              );