SQL Query To find out File Version

select sub.filename, sub.version
from (
   select adf.filename filename,
   afv.version version,
   rank()over(partition by adf.filename
     order by afv.version_segment1 desc,
     afv.version_segment2 desc,afv.version_segment3 desc,
     afv.version_segment4 desc,afv.version_segment5 desc,
     afv.version_segment6 desc,afv.version_segment7 desc,
     afv.version_segment8 desc,afv.version_segment9 desc,
     afv.version_segment10 desc,
     afv.translation_level desc) as rank1
   from ad_file_versions afv,
     (
     select filename, app_short_name, subdir, file_id
     from ad_files
     where upper(filename) like upper('%&filename%')
     ) adf
   where adf.file_id = afv.file_id
) sub
where rank1 = 1
order by 1