easier way to select row matching condition having maximum for a certain column

So I have a table that's basically a tree. Each row has a parent in the same table, and a lvl column that tells me the level in the tree, the root being lvl = 0. Now say I want to select all the rows that have a name starting with A, but only those that are on the highest level in this group. So the tree might have 9 levels, but the highest level containing a row with name starting with A might be on level 7, so I want all rows on level 7 starting with A (ignoring those on 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 with this is that I want to do something more complex than getting the rows starting with A, so 开发者_高级运维the condition would be something with a bit more clauses, and the select and subselect will have a few joins as well, and I don't like to repeat myself. Especially since I may need to modify this in the future, and I'm afraid I may forget some clause in one of the selects.

So is there an easier way of doing this, without having to repeat the select, to get the maximum level.

Analytics might help you here:

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 the removal of the self-join will be a performance improvement.