How to return the largest major and minor version for each product and each product can belong to 4 different environments?

@Jon Crawford

I attached an image of the desired outcome. I am trying to return the highest major and minor version for each product, for each respective environment. These products will be different for each admin, hence the "from AdminWindow". I must use SQL Server for this, I will not be able to use MySQL or anything else for that matter. It is returning the correct data but only for one product for example, product 1, I need it to reveal the neccesarry data for all products, 2,3,4, etc.

<code>select p.IdentifierId as ProductId, Major, Minor, e.Id as EnvironmentId
from AdminWindow a
join (SELECT distinct(id), OrganizationId FROM Environment GROUP BY Id, OrganizationId) e ON e.OrganizationId = a.OrganizationId
join (SELECT distinct(IdentifierId), MAX(Version_Major) as Major, MAX(Version_Minor) as Minor, AdminWindowId FROM Product GROUP BY IdentifierId, Version_Major, Version_Minor, AdminWindowId) t ON t.AdminWindowId = a.Id
group by t.IdentifierId, Major, Minor, e.Id
order by t.IdentifierId

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Apr 28, 2020 at 02:29 PM

ROW_NUMBER is your friend here. Use with GROUP BY for appropriate groupings, and ORDER BY for appropriate orderings (put them in descending order to make the next bit easier), and wrap that in a CTE based on your query.

Then select from that where that row_number is 1.

0 ·