-- I suggest you to break down your query in multiple cte for better clarity.
;with cte as
(
SELECT SampleID
,SetpointID
,CDPercentDiff
,AuditID
,pointrn = ROW_NUMBER()
OVER (
partition by SetpointID, AuditID
order by AuditID asc
,SetpointID asc
,SampleID desc
)
FROM [FSIStandards].[dbo].[Audit_Test_Results_backup]
WHERE AuditID in
(
SELECT [AuditID]
FROM [FSIStandards].[dbo].[Audit_Tests_backup]
WHERE Bench_QC_ID = 'CAL_006'
AND [PassFail] = 'Fail'
```)
```)
SELECT *
From
(
SELECT DISTINCT [MaxDiff] =
MAX(ABS([CDPercentDiff]))
OVER (Partition by AuditID order by AuditID)
,[AuditID]
(
SELECT SampleID
,SetpointID
,CDPercentDiff
,AuditID
,pointrn
FROM cte
WHERE
AuditID IN
(
SELECT [AuditID]
FROM [FSIStandards].[dbo].[Audit_Tests_backup]
where Bench_QC_ID = 'CAL_006'
AND [PassFail] = 'Fail'
)
AND pointrn = 1
OR pointrn = 2
)
``````) AS [MaxDiff]
WHERE [MaxDiff] < .15