title: Unable to fetch required data in SQL Server using group by
body: <p>I have the following data in one of my tables:</p>
  <pre><code>Id  subpartID     MainpartID    EngagementName  HashValue   Version CreatedDate ModifiedDate
  --------------------------------------------------------------------------------------------
  1   IN-2022         111112         name1         hash1        1     2022-05-26  NULL
  2   IN-2022         111112         name2         hash2        2     2022-05-26  NULL
  3   JP-2022         221112         name3         hash3        1     2022-05-26  NULL
  4   JP-2022         221112         name4         hash4        2     2022-05-26  NULL
  5   AU-2022         221112         name5         hash5        1     2022-05-26  NULL
  </code></pre>
  <p>I want to fetch records from it. When I enter a <code>MainpartID = 111112</code>, I want the output to be something like this:</p>
  <pre><code>Id  subpartID     MainpartID    EngagementName  HashValue   Version CreatedDate ModifiedDate
  --------------------------------------------------------------------------------------------
  2   IN-2022         111112         name2         hash2        2     2022-05-26  NULL
  </code></pre>
  <p>and when I enter <code>MainpartID = 221112</code>, I want the output to look like this:</p>
  <pre><code>Id  subpartID     MainpartID    EngagementName  HashValue   Version CreatedDate ModifiedDate
  --------------------------------------------------------------------------------------------
  4   JP-2022         221112         name4         hash4        2     2022-05-26  NULL
  5   AU-2022         221112         name5         hash5        1     2022-05-26  NULL
  </code></pre>
  <p>What the logic here is that for a particular MainpartID, fetch all subpartID's and then in the final output show only the latest version of a particular subpartID.</p>
  <p>My code is:</p>
  <pre><code>SELECT *
  FROM egTable
  WHERE egTable.[Version] = (SELECT MAX([Version]) 
                             FROM egTable 
                             WHERE MainpartID = '111112' 
                             GROUP BY subpartID) 
  </code></pre>
  <p>but this doesn't seem to work.</p>
  <p>Can someone help me with this? Data is dummy.</p>