title: How delete records that are not the maximum of a group from an SQL table
body: <p>I have a table with the following schema:</p>
  
  <pre><code>ID NVARCHAR(10)
  Message NVARCHAR(300)
  UpdateTime DATETIME
  </code></pre>
  
  <p>where ID is a foreign key. New message records are added in clusters with the same <code>UpdateTime</code>. An example would be (sorted by <code>ID</code> and <code>UpdateTime</code>):</p>
  
  <pre><code>ID   | Status        | UpdateTime
  ---------------------------------
  42     Cluster1-Msg1   2012-12-25
  42     Cluster1-Msg2   2012-12-25
  42     Cluster2-Msg1   2013-10-10
  42     Cluster2-Msg2   2013-10-10
  43     Cluster4-Msg1   2011-11-27
  </code></pre>
  
  <p>Here ID #42 is associated with 4 messages, clustered in two groups on different dates, while ID #43 is only associated with one message.</p>
  
  <p>From time to time I wish to purge this table by, for each group with the same ID, deleting all message records whose <code>UpdateTime</code> is less than the maximum within the group. The end result in the example above would be:</p>
  
  <pre><code>42     Cluster2-Msg1   2013-10-10
  42     Cluster2-Msg2   2013-10-10
  43     Cluster4-Msg1   2011-11-27
  </code></pre>
  
  <p>The following SQL query locates all the records I want to delete:</p>
  
  <pre><code>SELECT Msgs.ID, Msgs.UpdateTime
  FROM Messages Msgs
  JOIN
  (SELECT ID, MAX(UpdateTime) AS MaxTime FROM Messages GROUP BY ID) MaxTimes
  ON Msgs.ID = MaxDates.ID
  WHERE Msgs.UpdateTime &lt; MaxTimes.MaxTime
  </code></pre>
  
  <p>Now I wish to write a DELETE statement that removes the items that match those returned by the query above. The
  records must be removed based on the <code>ID</code> and <code>UpdateTime</code> values. I'm just not understanding how to express this in Transact-SQL.</p>