Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
SinghDestiny
Turn column into string
QLSV
List longest unique string for each id
BRYAN_BD Actul
Sequence object -- New Feature of 2012 sql server
q
/Users/svetlanakanevskaa/Downloads/ACDB.sql
testing-2
sql server dynamic 12 month time series and pivot with month year column names