MSSQL - Limit Query to last 5 records of group by
You can accomplish this using a Common Table Expression (CTE) that assigns a row number to each detector_record_id partitioned on each installation_id and detector_id.

Order it descending and return the top 5 in your outer query. This grabs the most recent 5 records (based on detector_record_id) per installation_id and detector_id. The outer query then sums the detector_record_status and a simple HAVING clause eliminates those that don't meet the "Active" threshold.

As your table grows, it will become important to monitor the inner SELECT performance as it will be returning all rows from detector_record and this could become slow to execute.

Example

;WITH dRecords AS (
  SELECT installation_id,
    detector_id,
    detector_record_status,
    ROW_NUMBER() OVER (PARTITION BY installation_id, detector_id ORDER BY detector_record_id DESC) AS [Rn]
  FROM detector_record
)
SELECT
  installation_id,
  detector_id,
  SUM(detector_record_status) as Activity
FROM dRecords
WHERE [Rn] &lt;= 5
GROUP BY   installation_id, detector_id
HAVING SUM(detector_record_status) &gt;= 3
ORDER BY   installation_id