Compare rows and select max value <p>I have the following table:</p> <pre><code>FileName | SubFileName | TotalPlayersCount | ------------------------------------------- AAA | SF1 | 11 | AAA | SF2 | 5 | AAA | SF3 | 3 | BBB | SF1 | 8 | BBB | SF2 | 15 | BBB | SF3 | 2 | CCC | SF1 | 5 | CCC | SF2 | 10 | CCC | SF3 | 20 | </code></pre> <p>As you can see, each <code>FileName</code> has 3 different <code>SubfileName</code> ('SF1', 'SF2', 'SF3').</p> <p>Each of these <code>SubfileName</code> have a different value for <code>TotalPlayersCount</code>.</p> <p>I am trying to select the max value of the column <code>TotalPlayersCount</code> out of the three <code>SubfileName</code>, and this, <em>FOREACH</em> <code>FileName</code>.</p> <p>The result should be:</p> <pre><code>FileName | SubFileName | TotalPlayersCount | ------------------------------------------- AAA | SF1 | 11 | BBB | SF2 | 15 | CCC | SF3 | 20 | </code></pre> <p>I tried myself a couple of queries and this is the closest I've come to:</p> <pre><code>select distinct FileName, max(TotalPlayersCount) AS TotalPlayersCount from dbo.MyTestTable group by FileName </code></pre> <p>This is the result I get:</p> <pre><code>FileName | TotalPlayersCount | ------------------------------ AAA | 11 | BBB | 15 | CCC | 20 | </code></pre> <p>So now I'm missing the <code>SubfileName</code> in the result.</p> <p>Could you help me finding what's missing?</p> <p>Thanks in advance.</p>