How to use Oracle’s CONNECT BY PRIOR to only get specific revisions of hierarchical records

You can't ever query for ROWNUM = 3; ROWNUM 1 gets filtered out, so the next record is now ROWNUM = 1, and so on.

I suspect being worried about controlling the computational depth is premature optimization. Unless you have millions of options being returned for a single user, pulling the highest level out of the data set - or even pulling the highest level for each dataset should be pretty simple. It's not as if you can't fully explore the hierarchy, so the additional work in the SQL engine of prioritizing/ranking each window of rows is pretty light on top of that.

So using aggregate/window functions seems like a natural choice here.

Your SQLFiddle doesn't return the dataset expected, but I've modified the SQL below to show how you can prioritize them, assuming I understand the requirements you're looking for.

Analytics rock.

select menu_level, menu_name, menu_id
from
(
select menu_level, menu_name, menu_id,
       row_number() over (partition by menu_name order by menu_level desc) as menu_priority
from
(
SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'adminname' 
--      AND ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to 
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id
  )
)
 where menu_priority = 1
 order by menu_name

MENU_LEVEL  MENU_NAME   MENU_ID
        3   MenuTab1    1
        2   MenuTab2    2
        1   MenuTab3    3

The dummy root is probably the right track, except you don't actually have to create a dummy root to make multiple roots. Your table also appears to be simultaneously an adjacency pair as well, so you've got a couple of immediate options on how you want to go using the table. Both should probably be tested for suitability, as I have a sneaky feeling that depending on read-a-lot or write-a-lot usage, your mileage from either case may vary.

Since the needs of both your developers and fellow DBAs must be considered, most of the other options are probably out of the question ( though there are loads and loads ). This SQL Fiddle illustrates a couple of ways to leverage the materialized paths inherit in your structure, first through leveraging a multiple-root HIERARCHYID, then through adjacency pair traversal through recursive CTEs.

To implement multiple roots, the only real "trick" to it is to preserve the sub-sequence on the root level. Instead of simply calling NULL descendants during root node creation, like so,

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( NULL, NULL ), '1' );

each root node can be created using the root as the base level ( though the first one obviously doesn't matter ):

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '1' );

INSERT INTO dbo.FailCode ( IDOrg, Name )
VALUES ( HIERARCHYID::GetRoot().GetDescendant( (
            SELECT  MAX( IDOrg )
            FROM    dbo.FailCode
            WHERE   IDOrg.GetAncestor( 1 ) = HIERARCHYID::GetRoot() ), NULL ),  '2' );

If you're in more of an UPDATE situtation, you can create the multiple root nodes off the primary key, if you like:

SELECT  CAST( '/' + CONVERT( VARCHAR( 16 ), ID ) + '/' AS HIERARCHYID ).ToString()
FROM    dbo.FailCode
WHERE   Name IN ( '1', '2' );

Children can then be added to each root node as necessary, business as usual stuff from there.

On the adjacency pairs side of your structure, either of the above inserts would do the trick - the ParentID for any root node just has to be NULL. Again, at this point, adding children is pretty evident.

Either way you go about it, the hierarchy capabilities are largely equivalent and either solution can be effective and efficient ( though the adjacency list model will certainly appear to be a little more involved! ).

HIERARCHYID

SELECT  Org = IDOrg.ToString(), ID, Name
FROM    dbo.FailCode
ORDER BY ID;

Adjacency List

;WITH cte_AdjacencyList AS (
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY ID ), 
            Org = CAST( '/' + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY ID ) ) + '/' AS VARCHAR( 32 ) ),
            ParentID, ID, Name
    FROM    dbo.FailCode 
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  Lvl = ROW_NUMBER() OVER (
                ORDER BY fc.ID ),
            Org = CAST ( al.Org + CONVERT( VARCHAR( 16 ), ROW_NUMBER() OVER (
                ORDER BY fc.ID ) ) + '/' AS VARCHAR( 32 ) ),
            fc.ParentID, fc.ID, fc.Name
    FROM    dbo.FailCode fc
    INNER JOIN cte_AdjacencyList al
        ON  fc.ParentID = al.ID )
SELECT  Org, ID, Name
FROM    cte_AdjacencyList
ORDER BY ID;