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.

closed as unclear what you're asking by Erik Darling, MDCCL, mustaccio, McNets, Marco Apr 12 '17 at 8:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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
  • 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.