Oracle SQL, how to filter on a set by a rank?
There are actually several ranking functions you can use. You can read at the Analytic functions at Oracle documentation. In your case, RANK() and DENSE_RANK() would work, if I have understood you:

select *
from
  ( select uc.*,
           DENSE_RANK() OVER (PARTITION BY ac.HEALTHSYSTEMID
                              ORDER BY ac.ACCESSLEVELTYPE ASC) 
             AS drnk
    from usercredential uc
      inner join users u 
              on u.userid = uc.userid
      inner join credential c 
              on c.credentialid = uc.credentialid
      inner join healthsystemcredential hsc 
              on hsc.credentialid = c.credentialid
      inner join accesslevel ac 
              on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
    where c.fulfillmentmethod = 'Attach'
    and c.display = 1
    and uc.auditstatus &lt;&gt; 0
    and ( (uc.auditstatus is null) 
       or (uc.auditstatus = 1 and uc.lastmodified &gt; uc.auditdate) 
        ) 
  ) tmp
where drnk = 1 ;


The condition on drnk = 1 (lowest Type Rank) will be applied of course after the other conditions in the internal where are applied. If you want to first filter on the lowest Type Rank (this may give you less rows in the final result), you can either group by in a derived table and then join:

select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE 
               FROM accesslevel 
               GROUP BY HEALTHSYSTEMID
             ) ac 
          on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus &lt;&gt; 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified &gt; uc.auditdate) 
    ) ;


or use a subquery in the ON clause. This is closer to your try but quite complicated. SQL-Server has CROSS APPLY which makes this kind of joins more easy to write: 

select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join accesslevel ac
          on ac.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         and ac.ACCESSLEVELTYPE = 
             ( SELECT MIN(aci.ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel aci
               WHERE aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID              
             )
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus &lt;&gt; 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified &gt; uc.auditdate) 
    ) ;


Tried at SQL-Fiddle. You can test with data in the tables, to see which one fits your requirements and whether the joins should be on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID or with some other column.