Unable to fetch required data in SQL Server using group by

Question

I have the following data in one of my tables:

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

I want to fetch records from it. When I enter a MainpartID = 111112, I want the output to be something like this:

Id  subpartID     MainpartID    EngagementName  HashValue   Version CreatedDate ModifiedDate
--------------------------------------------------------------------------------------------
2   IN-2022         111112         name2         hash2        2     2022-05-26  NULL

and when I enter MainpartID = 221112, I want the output to look like this:

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

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.

My code is:

SELECT *
FROM egTable
WHERE egTable.[Version] = (SELECT MAX([Version]) 
                           FROM egTable 
                           WHERE MainpartID = '111112' 
                           GROUP BY subpartID)

but this doesn't seem to work.

Can someone help me with this? Data is dummy.

Which column actually lets us know which subpart is the latest? You seem to have a tie in your sample data. — Tim Biegeleisen, May 30 '22 at 05:13
@BagusTesa Not enough. For `221112` there are two records tied with the same version. Which column(s) break that tie? — Tim Biegeleisen, May 30 '22 at 05:47
@TimBiegeleisen, the `subpartID` for both version 1 is different (one is `JP`, the other one is `AU`). i think, this is why op tried query to get latest version by grouping the `subpartID`. — Bagus Tesa, May 30 '22 at 05:49
yes @BagusTesa u r right. we want latest version of every `subpartID`. @Tim please take a look at desired output in question u will get better idea. — techbuster99, May 30 '22 at 05:52

score 3 · Answer 1 · answered May 30 '22 at 05:57

We can try using ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY MainpartID, subpartID
                                 ORDER BY Version DESC) rn
    FROM egTable
)

SELECT Id, subpartID, MainpartID, EngagementName, HashValue, Version,
       CreatedDate, ModifiedDate
FROM cte
WHERE rn = 1
ORDER BY MainpartID, subpartID;

score 0 · Answer 2 · answered May 30 '22 at 07:02

This is smilar to how you structured it, but you had to 'correlate' the subquery, ie you had to tell the subquery to run for a particular MainPartId and SubpartId. Also you had to filter the mainquery (if you wanted only one MainPArtID):

SELECT *
FROM egTable egt1
WHERE egt1.MainPartId='111112'
  and egt1.[Version] = (SELECT MAX([Version]) 
                        FROM egTable egt2
                        WHERE egt2.MainpartID = egt1.MainPartId
                          and egt2.SubPartID = egt1.SubPartID
                        )

Incidentally, each time you run the query for a different MainPartID you can of course provide the exact MainpartId to the subquery, in the manner you did.

Unable to fetch required data in SQL Server using group by

2 Answers2