title: SQL Group By and assign highest a value
body: <p>I am struggling with the SQL (mssql) to manipulate my data as i need it.  I have a table like this;</p>
  
  <pre><code>SOMEID, SOMEFIELD, DATE
  5        True    01-01-2010
  5        True    01-01-2011
  5        False   05-05-2012
  7        True    05-05-2011
  7        False   06-07-2015 
  </code></pre>
  
  <p>What I am trying to achieve is to add another column which assigns the value 1 if they are the most recent for that ID, and 0 if not. So in the above data example the new column values from top to bottom would be 0, 0, 1, 0, 1.</p>
  
  <p>I know I need to group by date but am having trouble assigning the values.</p>
  
  <p>Thanks for any pointers!</p>