title: Multiple MAX values select using inner join
body: <p>I have query that work for me only when values in the StakeValue don't repeat.
  Basically, I need to select maximum values from SI_STAKES table with their relations from two other tables grouped by internal type. </p>
  
  <pre><code>SELECT a.StakeValue, b.[StakeName], c.[ProviderName] 
  FROM SI_STAKES AS a 
  INNER JOIN SI_STAKESTYPES AS b ON a.[StakeTypeID] = b.[ID] 
  INNER JOIN SI_PROVIDERS AS c ON a.[ProviderID] = c.[ID] WHERE a.[EventID]=6 
    AND a.[StakeGroupTypeID]=1 
  AND a.StakeValue IN 
    (SELECT MAX(d.StakeValue) FROM SI_STAKES AS d 
     WHERE d.[EventID]=a.[EventID] AND d.[StakeGroupTypeID]=a.[StakeGroupTypeID] 
     GROUP BY d.[StakeTypeID])
  ORDER BY b.[StakeName], a.[StakeValue] DESC
  </code></pre>
  
  <p>Results for example must be:</p>
  
  <pre><code>[ID]  [MaxValue] [StakeTypeID] [ProviderName]
  1     1,5        6             provider1
  2     3,75       7             provider2
  3     7,6        8             provider3
  </code></pre>
  
  <p>Thank you for your help</p>