title: TSQL: Get a row which has a max value for a column
body: <p>I have a table with 2 columns (id, name) with following values:</p>
  
  <pre><code>id    name
  ---   ---
   1    John
   2    John 
   3    Mary
   4    Mary
  </code></pre>
  
  <p>For values that are repeated in 'name', I only want to select those rows which have maximum value in 'id'. So my desired output is:</p>
  
  <pre><code>id    name
  ---   ---
   2    John
   4    Mary
  </code></pre>
  
  <p>I tried following instructions from this link: <a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column">Fetch the row which has the Max value for a column</a> but couldn't get it to work.</p>
  
  <p>This is the query I'm using:</p>
  
  <pre><code>select 
      name, id 
  from
      (select 
           name, max(id) over (partition by name) max_ID 
       from sometable) 
  where 
      id = max_ID
  </code></pre>
  
  <p>But I'm getting this error:</p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'where'.</p>
  </blockquote>
  
  <p>Any ideas what I'm doing wrong?</p>