Here is a quick and simple window function solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH BASE_DATA(Heat_Index,HeatNo,Frame,Window) AS

(SELECT * FROM

(VALUES

(1, 584, 110, 110)

,(1, 584, 102, 109)

,(1, 584, 95, 106)

,(1, 584, 190, 112)

,(2, 586, 100, 100)

,(2, 586, 150, 120)

,(2, 586, 170, 130)

,(2, 586, 112, 126)

) AS X(Heat_Index,HeatNo,Frame,Window)

)

/* Find the maximum value of Frame for each heat

and the maximum value of Window for each heat.

This is done by partitioning the set on HeatNo.

*/

SELECT

BD.Heat_Index

,BD.HeatNo

,BD.Frame

,BD.Window

,MAX(BD.Frame) OVER

(

PARTITION BY BD.HeatNo

) AS MAX_Frame

,MAX(BD.Window) OVER

(

PARTITION BY BD.HeatNo

) AS MAX_Window

FROM BASE_DATA BD;

Results

Heat_Index HeatNo Frame Window MAX_Frame MAX_Window

----------- ----------- ----------- ----------- ----------- -----------

1 584 102 109 190 112

1 584 95 106 190 112

1 584 190 112 190 112

1 584 110 110 190 112

2 586 150 120 170 130

2 586 170 130 170 130

2 586 112 126 170 130

2 586 100 100 170 130