I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points". Basically I got two tables. ![alt text][1] Source table: This is a table with statistical per game averages for a category (category = column). Destination table or result set: This is a points table. If I have the highest average in a category I get maximum points which is same as number of teams. So if I have 10 teams and I have the highest average in 3PM category I get 10 points, and so on down to 1 point for the worst team in the category. Last column is a total of all points per team. The help I need: I would like to create a query that compares every teams value in a given column/category in the source table, ranks the results and gives the right amount of points. When finished it adds up the points per team to the Total PTS table. Appreciate it if someone would help me or show me the path to fantasy glory. [1]: /storage/temp/1935-tables.png

tables.png
(30.4 KiB)

Comment

Tom Staab

Best Answer

**Answer** by Tom Staab ·

There is a RANK function that should help a lot. WITH IndividualRanks AS ( SELECT Team , Rank3PM = RANK() OVER (ORDER BY [3PM]) , RankREB = RANK() OVER (ORDER BY [REB]) , RankAST = RANK() OVER (ORDER BY [AST]) , RankSTL = RANK() OVER (ORDER BY [STL]) , RankBLK = RANK() OVER (ORDER BY [BLK]) , RankPTS = RANK() OVER (ORDER BY [PTS]) FROM SourceTable ) SELECT Team , Rank3PM, RankREB, RankAST , RankSTL, RankBLK, RankPTS , TotalPoints = Rank3PM + RankREB + RankAST + RankSTL + RankBLK + RankPTS ORDER BY TotalPoints DESC -- show top ranked team first ;

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges