Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Ranking split across tournaments
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
-- 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;
View schema
Execution time: 0,02 sec, rows selected: 5, rows affected: 10, absolute service time: 0,17 sec
edit mode
|
history
|
discussion