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