Oracle SQL, how to filter on a set by a rank? I am struggling with the following SQL query right now:

http://sqlfiddle.com/#!4/736a7/12

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 hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
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) )


Basically I am trying to get UserCredential that are Many To One with Credential.  Credential is One To Many with HealthSystemCredential.

The HealthSystemCredential has a foreign key to AccessLevel, however this AccessLevel column in HealthSystemCredential is actually the Lowest Allowable Access Level.  AccessLevel records have a Type Rank between 1 at the lowest and 4 at the highest.


  Example: A HealthSystemCredential with an AccessLevel record that has a Type Rank of 2 should be fetched when I filter my query by AccessLevels with Types of 2, 3, or 4.


The problem is I can't figure out how to do this in one query.  Is there some way I can get a DENSE_RANK column in a subquery of AccessLevels, apply my filter in the subquery and join it to the table somehow to give me all the applicable HealthSystemCredentials I want?
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.