title: How to combine GROUP BY and ROW_NUMBER?
body: <p>I hope following sample code is self-explanatory:</p>
  
  <pre><code>declare @t1 table (ID int,Price money, Name varchar(10))
  declare @t2 table (ID int,Orders int,  Name varchar(10))
  declare @relation  table (t1ID int,t2ID int)
  insert into @t1 values(1, 200, 'AAA');
  insert into @t1 values(2, 150, 'BBB');
  insert into @t1 values(3, 100, 'CCC');
  insert into @t2 values(1,25,'aaa');
  insert into @t2 values(2,35,'bbb');
  insert into @relation values(1,1);
  insert into @relation values(2,1);
  insert into @relation values(3,2);
  
  select T2.ID AS T2ID
  ,T2.Name as T2Name
  ,T2.Orders
  ,T1.ID AS T1ID
  ,T1.Name As T1Name
  ,T1Sum.Price
  FROM @t2 T2
  INNER JOIN (
      SELECT Rel.t2ID
          ,MAX(Rel.t1ID)AS t1ID 
  -- the MAX returns an arbitrary ID, what i need is: 
  --      ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
          ,SUM(Price)AS Price
          FROM @t1 T1 
          INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
          GROUP BY Rel.t2ID
  )AS T1Sum ON  T1Sum.t2ID = T2.ID
  INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>T2ID   T2Name   Orders  T1ID    T1Name  Price     
   1      aaa       25     2       BBB    350,00     
   2      bbb       35     3       CCC    100,00
  </code></pre>
  
  <p>What i need is commented above, a way to get the <code>ROW_NUMBER</code> but also to <code>Group By</code> in the first place. So i need the <code>sum</code> of all T1-prices grouped by <code>T2.ID</code> in the relation-table and in the outer query the <code>t1ID</code> with the highest price. </p>
  
  <p>In other words: How to change <code>MAX(Rel.t1ID)AS t1ID</code> to somewhat returning the ID with the highest price?</p>
  
  <p>So the desired result is(notice that first T1ID changed from 2 to 1 since it has the higher price):</p>
  
  <pre><code>T2ID   T2Name   Orders  T1ID    T1Name  Price     
   1      aaa       25     1       AAA    350,00     
   2      bbb       35     3       CCC    100,00
  </code></pre>
  
  <p><strong>Note</strong>: in case you're wondering why i don't multiply <code>Orders</code> with Price: they are not realated(so i should have left off this column since it's a bit ambiguous, please ignore it, i've just added it to make all less abstract). Actually <code>Orders</code> must remain unchanged, that's the reason for the sub-query approach to join both and the reason why i need to group by in the first place.</p>
  
  <p><strong>Conclusion</strong>: obviously the core of my question can be answered by the <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer"><code>OVER</code> clause</a> that can be applied to <strong>any</strong> aggregate function like <code>SUM</code>(see <a href="https://stackoverflow.com/a/9618036/284240">Damien's answer</a>) what was new to me. Thank you all for your working approaches.</p>