Ranking split across tournaments, Sql Server

Ranking split across tournaments

-- http://stackoverflow.com/questions/37118669/sql-query-to-compare-two-tables-and-output-results-to-two-reports-in-proper-orde/37933970#37933970

create table VIG_FishWeight (FAAngler_ANID int not null, FWWeight decimal(5,2) not null);
insert into VIG_FishWeight (FAAngler_ANID, FWWeight) values
    (1, 2), (1, 4), (1, 7),
    (2, 10), (2, 1),
    (3, 5), (3, 1),
    (4, 3.5), (4, 3),
    (5, 1),
    (6, 2.3), (6, 7.1),
    (7, 3), (7, 3),
    (8, 5.1), (8, 0.9),
    (9, 1.2), (9, 1.3), (9, 2.5), (9, 3.5), (9, 8.9);

with individual as (
   select
        FAAngler_ANID as AnglerId,
        FWWeight as Weight,
        rank() over (order by FWWeight desc) as Rnk
    from VIG_FishWeight
), aggregate as (
    select
        FAAngler_ANID as AnglerId,
        sum(FWWeight) as Weight,
        rank() over (order by sum(FWWeight) desc) as Rnk
    from VIG_FishWeight
    group by FAAngler_ANID
), combined as (
    select
        i.AnglerId,
        /* Aggregate ranking must beat all the Individual rankings to win out.
           Remember there might be multiple fish on the Individual side. */
        case when min(a.Rnk) < min(i.Rnk) then 'Aggregate' else 'Big Fish' end as Tournament,
        case when min(a.Rnk) < min(i.Rnk) then min(a.Rnk) else min(i.Rnk) end as Rnk,
        case when min(a.Rnk) < min(i.Rnk) then max(a.Weight) else max(i.Weight) end as Weight
        --max(case when a.Rnk < i.Rnk then 'Aggregate' else 'Big Fish' end) as Tournament,
        --min(case when a.Rnk < i.Rnk then a.Rnk else i.Rnk end) as Rnk,
        --max(case when a.Rnk < i.Rnk then a.Weight else i.Weight end) as Weight
    from individual as i inner join aggregate as a on a.AnglerId = i.AnglerId
    group by i.AnglerId
), tournament as (
    select
        AnglerId,
        Tournament,
        Weight,
        rank() over (partition by Tournament order by Rnk) as TournamentRank
    from combined
)
select Tournament, TournamentRank, AnglerId, Weight
from tournament /* inner join to Anglers table for name, etc. */
where TournamentRank <= 25
order by Tournament, TournamentRank;


run \| edit \| history \| help	0