SQL query to get the version of a file

select sub.filename, sub.version , sub.last_update_date, sub.app_short_name, sub.subdir from ( select adf.filename filename, afv.version version,afv.LAST_UPDATE_DATE,adf.app_short_name, adf.subdir, 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;