2

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.

| improve this question | | | | |
  • 1
    Could you please give us a bit more of the table layout? Can you give us some examples of the revision number scheme? Are version and revisions INT values? – Wes Apr 11 '17 at 16:01
  • 1
    So, revision 12 of version 1 should supersede rev. 1 of version 10? – mustaccio Apr 11 '17 at 17:20
6

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
| improve this answer | | | | |
  • Would using max over partition by filename also be an acceptable answer? I feel like I may be reading OP's question wrong. – Shaulinator Apr 11 '17 at 16:06
  • 2
    @Shaulinator I don't see why it wouldn't be possible to write a solution using max() over (partition by ...) but it may be more work to get both the latest version and the latest revision for that version using that approach. – SqlZim Apr 11 '17 at 16:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.