title: SQL select the row with max value using row_number() or rank()
body: <p>I have data of following kind:</p>
  
  <pre><code>RowId   Name    Value
  1       s1      12
  22      s1      3
  13      s1      4
  10      s2      14
  22      s2      5
  3       s2      100
  </code></pre>
  
  <p>I want to have the following output:</p>
  
  <pre><code>RowId   Name    Value
  1       s1      12
  3       s2      100
  </code></pre>
  
  <p>I am currently using temp tables to get this in two step. I have been trying to use row_number() and rank() functions but have not been successful. </p>
  
  <p>Can someone please help me with syntax as I feel row_number() and rank() will make it cleaner?</p>
  
  <p>Edit:
  I changed the rowId to make it a general case</p>
  
  <p>Edit:
  I am open to ideas better than row_number() and rank() if there are any.</p>