title: RANK partition function used in conjunction with SUM OVER
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>How do I simplify the following?:</p>
  
  <pre><code>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
  </code></pre>