let me show You a simple table at first:

Look at the value '00006693'. I have to get max value from ver column: this will be '0025' in this case. However, if all values in the column are the same, then I have to get max value from inr column. this will be '00024727' in this case. The result should eventually look like:

My question is, how to get this data by using two max() functions? I am not good at analytical issues.

I will also add an image, where the problem is described (I think) better and please base on it:

CodePudding user response：

I can offer two options. Simple solution - just sort by "ver desc, inr desc" within party_id group:

with test_data as (
  select '00020222' inr,    '00020107' party_id,    '0001' ver union
  select '00006692',    '00006693', '0025' union
  select '00021768',    '00006693', '0006' union
  select '00024726',    '00006693', '0001' union
  select '00024727',    '00006693', '0000' union
  select '00006691',    '00006692', '0018' union
  select '00021949',    '00006692', '0000' union
  select '00024728',    '00006692', '0000' union
  select '00034567',    '00019734', '0022' union
  select '00064657',    '00019734', '0022'
)
select
  r.inr, r.party_id, r.ver
from 
(  
  select 
    t.*, 
    row_number() over(partition by t.party_id order by t.ver desc, t.inr desc) rn
  from
    test_data t
) r
where
  r.rn = 1

Second solution more verbose, but implements your logic directly:

with test_data as (
  select '00020222' inr,    '00020107' party_id,    '0001' ver union
  select '00006692',    '00006693', '0025' union
  select '00021768',    '00006693', '0006' union
  select '00024726',    '00006693', '0001' union
  select '00024727',    '00006693', '0000' union
  select '00006691',    '00006692', '0018' union
  select '00021949',    '00006692', '0000' union
  select '00024728',    '00006692', '0000' union
  select '00034567',    '00019734', '0022' union
  select '00064657',    '00019734', '0022'
)
select 
  r.inr, r.party_id, r.ver
from
(  
  select 
    t.*,
    case when count(distinct t.ver) over(partition by t.party_id) == 1 then 1 else 0 end is_all_ver_same,
    row_number() over(partition by t.party_id order by t.ver desc) max_ver,
    row_number() over(partition by t.party_id order by t.inr desc) max_inr
  from 
    test_data t
) r   
where 
  (r.is_all_ver_same = 1 and r.max_inr = 1) or (r.is_all_ver_same = 0 and r.max_ver = 1)

CodePudding user response：

I think it's not doable in one select statement since window functions has their limitations but what you need can be solved with a subquery with a ROW_NUMBER() function for ordering:

select 
    inr,
    party_id,
    ver
from
    (
        select
            row_number() over (partition by party_id order by ver desc, inr desc) as rn_ver,
            *
        from #Temp
    ) a
where rn_ver = 1