title: SQL Selecting distinct rows from multiple columns based on max value in one column
body: <p>This is my SQL View - lets call it MyView :</p>
  
  <pre>
  ECode   SHCode  TotalNrShare    CountryCode Country
  000001  +00010  100 UKI United Kingdom
  000001  ABENSO  900 USA United States
  000355  +00012  1000    ESP Spain
  000355  000010  50  FRA France
  000042  009999  10  GER Germany
  000042  +00012  999 ESP Spain
  000787  ABENSO  500 USA United States
  000787  000150  500 ITA Italy
  001010  009999  100 GER Germany
  </pre>
  
  <p>I would like to return the single row with the highest number in the column TotalNrShare for each ECode.</p>
  
  <p>For example, I’d like to return these results from the above view:</p>
  
  <pre>
  ECode   SHCode  TotalNrShare    CountryCode Country
  000001  ABENSO  900 USA United States
  000355  +00012  1000    ESP Spain
  000042  +00012  999 ESP Spain
  000787  ABENSO  500 USA United States
  001010  009999  100 GER Germany
  </pre>
  
  <p>(note in the case of ECode 000787 where there are two SHCode's with 500 each, as they are the same amount we can just return the first row rather than both, it isnt important for me which row is returned since this will happen very rarely and my analysis doesnt need to be 100%)</p>
  
  <p>Ive tried various things but do not seem to be able to return either unqiue results or the additional country code/country info that I need.</p>
  
  <p>This is one of my attempts (based on other solutions on this site, but I am doing something wrong):</p>
  
  <pre><code>SELECT tsh.ECode, tsh.SHCode, tsh.TotalNrShare, tsh.CountryCode, tsh.Country
  FROM  dbo.MyView AS tsh INNER JOIN
                     (SELECT DISTINCT ECode, MAX(TotalNrShare) AS MaxTotalSH
                      FROM   dbo.MyView
                      GROUP BY ECode) AS groupedtsh ON tsh.ECode = groupedtsh.ECode AND tsh.TotalNrShare = groupedtsh.MaxTotalSH
  </code></pre>