• You can use a cte in a view so this should work for you.

    ;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

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/