select smi, min(rank), agg_median(rank), tanimoto('NCCc1ccccc1', smi) tani from
(
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'Cc1ccc(CCN)cc1', 100) > 0.0
      order by tanimoto(smi, 'Cc1ccc(CCN)cc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'CC(CN)c1ccccc1', 100) > 0.0
      order by tanimoto(smi, 'CC(CN)c1ccccc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'CCc1ccc(CCN)cc1', 100) > 0.0
      order by tanimoto(smi, 'CCc1ccc(CCN)cc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'CNCCc1ccccc1', 100) > 0.0
      order by tanimoto(smi, 'CNCCc1ccccc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'CC(C)c1ccc(CCN)cc1', 100) > 0.0
      order by tanimoto(smi, 'CC(C)c1ccc(CCN)cc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'NCCc1ccccc1', 100) > 0.0
      order by tanimoto(smi, 'NCCc1ccccc1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'Cc1cccc(CCN)c1', 100) > 0.0
      order by tanimoto(smi, 'Cc1cccc(CCN)c1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'Cc1cc(C)cc(CCN)c1', 100) > 0.0
      order by tanimoto(smi, 'Cc1cc(C)cc(CCN)c1') desc )
  union all
  select smi, rownum rank from
    ( select distinct(smi) from nci 
      where tanimoto(smi, 'CC(C)(CN)c1ccccc1', 100) > 0.0
      order by tanimoto(smi, 'CC(C)(CN)c1ccccc1') desc )
)
group by smi
 order by 2 asc, 3 desc;