title: SQL Server Group by guid but order entire query by most recent in group
body: <p>I have a query where I'd like to group things by a certain GUID but I want to order a single group by date (easy) and then order the entire results set based on the most recent record in each group and have that order listed as the results set. I want them to stick together. Does that make sense?</p>
  
  <p>The best I could come up with is:</p>
  
  <pre><code>SELECT 
     ID, data, Date, 
     row_number() OVER (PARTITION BY GUID ORDER BY Date DESC) AS rn 
  FROM 
     MyTable
  </code></pre>
  
  <p>But this doesn't take into consideration the date for the entire results set.</p>
  
  <p>Default table</p>
  
  <pre><code>ID      data                Date                        GUID
  0       mapper started      2012-10-16 12:18:52     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  1       view models init    2012-10-16 12:18:53     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  2       view load           2012-10-16 12:18:54     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  3       mapper load         2012-10-16 12:18:55     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  4       view finished       2012-10-16 12:18:56     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  5       mapper finished     2012-10-16 12:18:57     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  </code></pre>
  
  <p>Query output I want:</p>
  
  <pre><code>ID      data                Date                         GUID
  5       mapper finished     2012-10-16 12:18:57     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  3       mapper load         2012-10-16 12:18:55     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  0       mapper started      2012-10-16 12:18:52     SessionID:[376f795a-dba2-49e1-8047-dbac9141d78b]
  4       view finished       2012-10-16 12:18:56     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  2       view load           2012-10-16 12:18:54     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  1       view models init    2012-10-16 12:18:53     SessionID:[2a1be1ce-606a-44ca-8400-75b7fd23d09a]
  </code></pre>