SELECT A.COMPKEY, B.HYDR_ID, B.DATE, B.hydr_gpm, B.gpm_20, B.gpm_10, B.gpm_0, B.staff_id, B.shift, 
		B.static, B.residual, B.pitot, B.pitot2, coeff, A.Shape, C.not_in_srv, A.FLOWTEST, 
		Rank() over (Partition BY B.HYDR_ID order by B.DTTM DESC) AS RANK
	INTO #FLOWTEST
		FROM GISOWNER.WATHYDRANT A
			LEFT JOIN S_4.FH.dbo.hyd_actv B
				ON A.COMPKEY = B.HYDR_ID
			LEFT JOIN S_4.FH.dbo.hyd_main C
				ON A.COMPKEY = C.HYDR_ID
	WHERE A.FIREZONE IN ('FZ1', 'FZ2', 'FZ3', 'FZ4', 'FZ5', 'FZ6', 'FZ7') AND (B.FLOWTEST = '1' OR C.not_in_srv = '1')

	SELECT * FROM #FLOWTEST WHERE RANK = '1'
	DROP TABLE #FLOWTEST

;with FLOWTEST as
(
	SELECT A.COMPKEY, B.HYDR_ID, B.DATE, B.hydr_gpm, B.gpm_20, B.gpm_10, B.gpm_0, B.staff_id, B.shift, 
		B.static, B.residual, B.pitot, B.pitot2, coeff, A.Shape, C.not_in_srv, A.FLOWTEST, 
		Rank() over (Partition BY B.HYDR_ID order by B.DTTM DESC) AS RANK	
		FROM GISOWNER.WATHYDRANT A
			LEFT JOIN S_4.FH.dbo.hyd_actv B
				ON A.COMPKEY = B.HYDR_ID
			LEFT JOIN S_4.FH.dbo.hyd_main C
				ON A.COMPKEY = C.HYDR_ID
	WHERE A.FIREZONE IN ('FZ1', 'FZ2', 'FZ3', 'FZ4', 'FZ5', 'FZ6', 'FZ7') AND (B.FLOWTEST = '1' OR C.not_in_srv = '1')
)
	SELECT * FROM FLOWTEST WHERE RANK = 1

SELECT *
FROM 
(
	SELECT A.COMPKEY, B.HYDR_ID, B.DATE, B.hydr_gpm, B.gpm_20, B.gpm_10, B.gpm_0, B.staff_id, B.shift, 
		B.static, B.residual, B.pitot, B.pitot2, coeff, A.Shape, C.not_in_srv, A.FLOWTEST, 
		Rank() over (Partition BY B.HYDR_ID order by B.DTTM DESC) AS RANK	
		FROM GISOWNER.WATHYDRANT A
			LEFT JOIN S_4.FH.dbo.hyd_actv B
				ON A.COMPKEY = B.HYDR_ID
			LEFT JOIN S_4.FH.dbo.hyd_main C
				ON A.COMPKEY = C.HYDR_ID
	WHERE A.FIREZONE IN ('FZ1', 'FZ2', 'FZ3', 'FZ4', 'FZ5', 'FZ6', 'FZ7') AND (B.FLOWTEST = '1' OR C.not_in_srv = '1')
) FLOWTEST 
WHERE RANK = 1