RANK partition function used in conjunction with SUM OVER

sql, sql-server-2008-r2

Solution

There is a simpler way. Try this:

select t.*,
       RANK() over (order by sumscore desc, score desc)
from (select t.*,
             SUM(score) over (partition by department) as SumScore
       from #Table t
      ) t

Problem

I have the following which works but I think I've done my usual trick of over complicating something which could be a lot simpler. If you run the script you will see what I'm trying to achieve - simply a rank initially by Department score and then a rank by each name's score within each department. How do I simplify the following?: ``` IF OBJECT_ID('TEMPDB..#Table') IS NOT NULL BEGIN DROP TABLE #Table END; CREATE TABLE #Table ( Department VARCHAR(100), Name VARCHAR(100), Score INT ); INSERT INTO #Table VALUES ('Sales','michaeljackson',7), ('Sales','jim',10), ('Sales','jill',66), ('Sales','j',1), ('DataAnalysis','jagoda',66), ('DataAnalysis','phil',5), ('DataAnalysis','jesus',6), ('DataAnalysis','sam',79), ('DataAnalysis','michaeljackson',9999); WITH SumCte AS ( SELECT Department, sm = sum(Score) FROM #Table GROUP BY Department ) , RnkDepCte AS ( SELECT Department, rk =RANK() OVER (ORDER BY sm DESC) FROM SumCte ) , RnkCte AS ( SELECT Department, Name, Score, rnk = RANK() OVER (PARTITION BY a.Department ORDER BY a.Score DESC) FROM #Table a ) SELECT a.Department, a.Name, a.Score, FinalRank = RANK() OVER (ORDER BY ((10000/b.rk) + (100/a.rnk)) DESC) FROM RnkCte a INNER JOIN RnkDepCte b ON a.Department = b.Department ```

Original source