How to get max of combined columns? I have a table with the following columns:
filename, filetype, version and revision.

I want to code a query that will find the highest version AND revision for a filename and filetype. For example, the following will work:

select 
 filename 
,filetype
,max(version + revision) as combined
from dmkr_asline.cmires_lbyi
group by filename, filetype
order by filename, filetype;


However, I want to be able to return version and revision as separate columns so I can join another table. I cannot use max on each column because I want the max revision of the max version.
Using top with ties with row_number() to get the row with the highest revision for the highest version for each filename and filetype.

select top 1 with ties
    filename 
  , filetype
  , version
  , revision
from dmkr_asline.cmires_lbyi
order by row_number() over (
  partition by filename, filetype 
  order by version desc, revision desc
  );




Alternative using a common table expression with row_number()

;with cte as (
  select
      filename 
    , filetype
    , version
    , revision
    , rn = row_number() over (
        partition by filename, filetype 
        order by version desc, revision desc
        )
  from dmkr_asline.cmires_lbyi
)
select 
      filename 
    , filetype
    , version
    , revision
from cte
where rn = 1
order by filename, filetype




cross apply() version:

select distinct
    t.filename 
  , t.filetype
  , x.version
  , x.revision
from dmkr_asline.cmires_lbyi t
  cross apply (
    select top 1
        i.version
      , i.revision
    from dmkr_asline.cmires_lbyi as i
    where i.filename = t.filename
      and i.filetype = t.filetype
    order by i.version desc, i.revision desc
    ) as x




inner join version:

select 
    t.filename 
  , t.filetype
  , x.version
  , revision = max(t.revision)
from dmkr_asline.cmires_lbyi t
  inner join (
    select 
        i.filename
      , i.filetype
      , version = max(i.version)
    from dmkr_asline.cmires_lbyi i
    group by
        i.filename
      , i.filetype
    ) as x
    on x.filename = t.filename
   and x.filetype = t.filetype
   and x.version  = t.version
group by
    t.filename 
  , t.filetype
  , x.version