OLD VERSION

CREATE OR REPLACE VIEW INTHARM_A2_INJ (MEMBER_RANK, ID, MAGNET_NAME, MAGNET_TYPE, MAGNET_STATE, APERTURE_NUMBER, MEASURE_DATE, INTEGRAL_TYPE, SET_CURRENT, RAMP_DIRECTION, RAMP_RATE, TEMPERATURE, REFERENCE_RADIUS, NORMALIZATION_ORDER, MAGNET_X_CENTER, MAGNET_Y_CENTER, NORMAL_1, SKEW_1, NORMAL_2, SKEW_2, NORMAL_3, SKEW_3, NORMAL_4, SKEW_4, NORMAL_5, SKEW_5, NORMAL_6, SKEW_6, NORMAL_7, SKEW_7, NORMAL_8, SKEW_8, NORMAL_9, SKEW_9, NORMAL_10, SKEW_10, NORMAL_11, SKEW_11, NORMAL_12, SKEW_12, NORMAL_13, SKEW_13, NORMAL_14, SKEW_14, NORMAL_15, SKEW_15, LOGIN_NAME, LOGIN_DATE, NOTES) AS
select V."MEMBER_RANK",V."ID",V."MAGNET_NAME",V."MAGNET_TYPE",V."MAGNET_STATE",V."APERTURE_NUMBER",V."MEASURE_DATE",V."INTEGRAL_TYPE",V."SET_CURRENT",V."RAMP_DIRECTION",V."RAMP_RATE",V."TEMPERATURE",V."REFERENCE_RADIUS",V."NORMALIZATION_ORDER",V."MAGNET_X_CENTER",V."MAGNET_Y_CENTER",V."NORMAL_1",V."SKEW_1",V."NORMAL_2",V."SKEW_2",V."NORMAL_3",V."SKEW_3",V."NORMAL_4",V."SKEW_4",V."NORMAL_5",V."SKEW_5",V."NORMAL_6",V."SKEW_6",V."NORMAL_7",V."SKEW_7",V."NORMAL_8",V."SKEW_8",V."NORMAL_9",V."SKEW_9",V."NORMAL_10",V."SKEW_10",V."NORMAL_11",V."SKEW_11",V."NORMAL_12",V."SKEW_12",V."NORMAL_13",V."SKEW_13",V."NORMAL_14",V."SKEW_14",V."NORMAL_15",V."SKEW_15",V."LOGIN_NAME",V."LOGIN_DATE",V."NOTES" from (
SELECT rank() over(partition by T.MAGNET_NAME order by T.measure_date desc) as member_rank, T.*
FROM F_INT_HARM T
WHERE
((T.MAGNET_TYPE = 1 AND T.SET_CURRENT BETWEEN 760-1 AND 760+1)
OR (T.MAGNET_TYPE = 2 AND T.SET_CURRENT BETWEEN 760-1 AND 760+1))
AND T.APERTURE_NUMBER = 2
AND T.MAGNET_STATE = 'cryomagnet'
AND T.INTEGRAL_TYPE = 'full'
AND T.RAMP_RATE BETWEEN -0.05 AND 0.05
AND T.RAMP_DIRECTION = 1
AND T.TEMPERATURE BETWEEN 1.5 AND 2.1
ORDER BY T.MAGNET_NAME, T.APERTURE_NUMBER, round(T.SET_CURRENT), member_rank
) V where member_rank = 1;

NEW VERSION

CREATE OR REPLACE VIEW INTHARM_A2_INJ (ID, MAGNET_NAME, MAGNET_TYPE, MAGNET_STATE, APERTURE_NUMBER, MEASURE_DATE, INTEGRAL_TYPE, SET_CURRENT, RAMP_DIRECTION, RAMP_RATE, TEMPERATURE, REFERENCE_RADIUS, NORMALIZATION_ORDER, MAGNET_X_CENTER, MAGNET_Y_CENTER, NORMAL_1, SKEW_1, NORMAL_2, SKEW_2, NORMAL_3, SKEW_3, NORMAL_4, SKEW_4, NORMAL_5, SKEW_5, NORMAL_6, SKEW_6, NORMAL_7, SKEW_7, NORMAL_8, SKEW_8, NORMAL_9, SKEW_9, NORMAL_10, SKEW_10, NORMAL_11, SKEW_11, NORMAL_12, SKEW_12, NORMAL_13, SKEW_13, NORMAL_14, SKEW_14, NORMAL_15, SKEW_15, LOGIN_NAME, LOGIN_DATE, NOTES) AS
select V."ID",V."MAGNET_NAME",V."MAGNET_TYPE",V."MAGNET_STATE",V."APERTURE_NUMBER",V."MEASURE_DATE",V."INTEGRAL_TYPE",V."SET_CURRENT",V."RAMP_DIRECTION",V."RAMP_RATE",V."TEMPERATURE",V."REFERENCE_RADIUS",V."NORMALIZATION_ORDER",V."MAGNET_X_CENTER",V."MAGNET_Y_CENTER",V."NORMAL_1",V."SKEW_1",V."NORMAL_2",V."SKEW_2",V."NORMAL_3",V."SKEW_3",V."NORMAL_4",V."SKEW_4",V."NORMAL_5",V."SKEW_5",V."NORMAL_6",V."SKEW_6",V."NORMAL_7",V."SKEW_7",V."NORMAL_8",V."SKEW_8",V."NORMAL_9",V."SKEW_9",V."NORMAL_10",V."SKEW_10",V."NORMAL_11",V."SKEW_11",V."NORMAL_12",V."SKEW_12",V."NORMAL_13",V."SKEW_13",V."NORMAL_14",V."SKEW_14",V."NORMAL_15",V."SKEW_15",V."LOGIN_NAME",V."LOGIN_DATE",V."NOTES" from (
SELECT rank() over(partition by T.MAGNET_NAME order by T.measure_date desc) as member_rank, T.*
FROM F_INT_HARM T
WHERE
((T.MAGNET_TYPE = 1 AND T.SET_CURRENT BETWEEN 760-1 AND 760+1)
OR (T.MAGNET_TYPE = 2 AND T.SET_CURRENT BETWEEN 760-1 AND 760+1))
AND T.APERTURE_NUMBER = 2
AND T.MAGNET_STATE = 'cryomagnet'
AND T.INTEGRAL_TYPE = 'full'
AND T.RAMP_RATE BETWEEN -0.05 AND 0.05
AND T.RAMP_DIRECTION = 1
AND T.TEMPERATURE BETWEEN 1.5 AND 2.1
) V where member_rank = 1;