Использование оконной функции для получения максимального значения по нескольким записям - SQL

Я все еще новичок в SQL, и у меня возникают проблемы с получением максимального значения для нескольких записей в SQL. Обычно я использую представление и присоединяю его к моей хранимой процедуре, но мне нужно иметь возможность делать это без представлений из-за динамических имен таблиц.

Вот мой код:

SELECT     rcl.CaseCaseId AS CaseId, cc.Label AS CaseName, rpt.RptDateCum, cp.Label, rpt.CorpProductId, corp.Label AS Scenario, proj.Label AS Project, 
                      ProjectScenario.ReportDate
into #CumTemp
FROM         PhdRpt.ReportCaseList_542 AS rcl INNER JOIN
                      CaseScenario AS cs ON rcl.CaseCaseId = cs.CaseCaseId INNER JOIN
                      CorpScenario AS corp ON cs.CorpScenarioId = corp.CorpScenarioId INNER JOIN
                      CaseCases AS cc ON cs.CaseCaseId = cc.CaseCaseId INNER JOIN
                      PhdRpt.RptProductTech_542 AS rpt ON rcl.ReportId = rpt.ReportID AND rcl.ReportRunCaseId = rpt.RptCaseId INNER JOIN
                      CorpProduct AS cp ON rpt.CorpProductId = cp.CorpProductId INNER JOIN
                      ProjProject AS proj ON cs.ProjProjectId = proj.ProjProjectId INNER JOIN
                      ProjectScenario ON cs.ProjProjectId = ProjectScenario.ProjProjectId AND cs.CorpScenarioId = ProjectScenario.CorpScenarioId AND 
                      corp.CorpScenarioId = ProjectScenario.CorpScenarioId AND proj.ProjProjectId = ProjectScenario.ProjProjectId AND 
                      proj.ProjProjectId = ProjectScenario.ProjProjectId
WHERE     (cs.CorpScenarioId = 1) AND (cs.Deleted = 0)
ORDER BY CaseId


Select  ROW_NUMBER() OVER(order by caseid) as Row, CaseId, Scenario,Project, ReportDate, ltrim(str(sum(Gas))) as Gas, ltrim(str(SUM(Oil))) as Oil, ltrim(str(SUM(Water))) as Water
,MAX(oil) over (partition by Project) as oilmax, MAX(gas) over (partition by Project) as gasmax, MAX(water) over (partition by Project) as watermax
from #CumTemp
pivot (max (RptDateCum) for Label in ([Gas], [Oil], [Water]))as x
group by CaseId, Scenario,Project, ReportDate, Oil
order by Row 
drop table #CumTemp

Я пытаюсь сравнить записи и найти максимальное значение для газа, нефти и воды и создать 3 новых столбца, в которых будут эти значения. Причина, по которой я не хочу использовать группу по, заключается в том, что мне все еще нужен доступ к каждой отдельной записи. Все записи принадлежат одному проекту.

Мой желаемый результат будет выглядеть примерно так.

id      oil    gas    water   oilmax    gasmax    watermax   
----------------------------------------------------------
 1       25     12       6      51        98         37
 2       36     98      19      51        98         37
 3       51     19      22      51        98         37
 4       45     77      37      51        98         37
 5       22     64      31      51        98         37

Когда я использую свой код, он дает мне несколько строк на запись, когда мне нужна только одна строка на запись. Есть ли другой способ добиться этого без использования представлений или оконных функций?