title: SQL Server : group by with corresponding row values
body: <p>I need to write a T-SQL group by query for a table with multiple dates and seq columns:</p>
  
  <pre><code>DROP TABLE #temp
  CREATE TABLE #temp(
         id char(1),
         dt DateTime,
         seq int)
  
  Insert into #temp values('A','2015-03-31 10:00:00',1)
  Insert into #temp values('A','2015-08-31 10:00:00',2)
  Insert into #temp values('A','2015-03-31 10:00:00',5)
  Insert into #temp values('B','2015-09-01 10:00:00',1)
  Insert into #temp values('B','2015-09-01 10:00:00',2)
  </code></pre>
  
  <p>I want the results to contains only the items A,B with their <em>latest date</em> and the <em>corresponding</em> seq number, like:</p>
  
  <pre><code>id  MaxDate CorrespondentSeq
  A   2015-08-31 10:00:00.000 2
  B   2015-09-01 10:00:00.000 2
  </code></pre>
  
  <p>I am trying with (the obviously wrong!):</p>
  
  <pre><code>select id, max(dt) as MaxDate, max(seq) as CorrespondentSeq
  from #temp
  group by id
  </code></pre>
  
  <p>which returns:</p>
  
  <pre><code>id  MaxDate CorrespondentSeq
  A   2015-08-31 10:00:00.000 5  &lt;-- 5 is wrong
  B   2015-09-01 10:00:00.000 2
  </code></pre>
  
  <p>How can I achieve that?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>The <code>dt datetime</code> column has duplicated values (exactly same date!)</p>
  
  <p>I am using SQL Server 2005</p>