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

Question

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.

Answer 1

Hi Andrei,

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.