An easier way to select a row matching condition that has a maximum for a specific column

Question

So, I have a table, basically a tree. Each row has a parent in the same table and an lvl column that tells me the level in the tree whose root is lvl = 0. Now say that I want to select all rows that have a name starting with A, but only those that are at the highest level in this group. So a tree can have 9 levels, but the highest level containing a line with a name starting with A can be at level 7, so I want all lines at level 7 to start with A (ignoring them at lower levels) . I could do something like this:

select id, name, lvl
  from my_table
 where name like 'A%'
   and lvl = (select max(lvl) from my_table
               where name like 'A%')

The problem is that I want to do something more complicated than getting lines starting with A, so the condition will be something with a bit more caveats, and the selection and subtitle will have several joins, and I also don't like to repeat. Moreover, I may need to change this in the future, and I am afraid that I may forget some suggestion in one of the options.

So, there is an easier way to do this without repeating the choice in order to get the maximum level.

Answer 1

Google Analytics can help you:

SELECT ID, NAME, rn
  FROM (SELECT id, NAME, rank() over(ORDER BY LVL DESC) rn 
          FROM my_table 
         WHERE NAME LIKE 'A%')
 WHERE rn = 1

As an added benefit in most cases, eliminating self-connection will be an improvement in performance.