Here is another possibility. Let me say before hand that Jeff Moden's solution is better than this for the specific task you asked for; (on my laptop, my solution is 85% of the cost, whereas jeff's solution is 15% of the cost)

But in case you want to assign sequential ids to each group and sequential ids to rows within a group, you can consider this approach; With this approach, you can answer, for example, what is the average pressure of the 15th group after 10/24/2012.

; WITH R (MachineId, Pressure, RecordedDate, Rid) AS

(

SELECT MachineId, Pressure, RecordedDate,

ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY MachineId, RecordedDate) AS Rid

FROM dbo.UpdateGaps

),

GRPROW(MachineId, Pressure, RecordedDate, GroupId, RowId) AS

(

SELECT G1.MachineId, G1.Pressure, G1.RecordedDate,

DENSE_RANK() OVER (PARTITION BY G1.MachineId ORDER BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid)) AS GroupId,

ROW_NUMBER() OVER (PARTITION BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid) ORDER BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid), G1.RecordedDate DESC) AS RowId

FROM R G1

LEFT JOIN R G2 ON (G1.MachineID = G2.MachineID

AND G2.Rid > G1.Rid

AND G1.Pressure <> G2.Pressure)

GROUP BY G1.MachineId, G1.Pressure, G1.RecordedDate, G1.Rid

)

SELECT G1.MachineId, G1.Pressure, G1.RecordedDate, G1.GroupId, G1.RowId AS DescendingRowId,

CASE WHEN ISNULL(G2.Pressure, 0) = 0 THEN G1.Pressure ELSE G2.Pressure END AS NewPressure

FROM GRPROW G1

LEFT JOIN GRPROW G2 ON (G1.MachineId = G2.MachineId

AND G1.GroupId - 1 = G2.GroupId

AND G2.RowId = 1)

ORDER BY G1.MachineId, G1.RecordedDate