SELECT
    HC.CHECKUP_DATE AS CK_DATE
,   HC.MEMBER_CODE AS CODE
,   MM.LAST_NAME || MM.FIRST_NAME AS NAME
,   ROUND(HC.WEIGHT - HC_S.WEIGHT, 1) AS CHG_WT
FROM
    HEALTH_CHECKUP AS HC
LEFT JOIN MEMBER_MST AS MM
    ON  MM.MEMBER_CODE = HC.MEMBER_CODE
LEFT JOIN
(
    SELECT
        MEMBER_CODE
    ,   WEIGHT
    ,   MAX(CHECKUP_DATE)
    FROM
        HEALTH_CHECKUP
    WHERE
        CHECKUP_DATE < (
                            SELECT
                                MAX(CHECKUP_DATE)
                            FROM
                                HEALTH_CHECKUP
                        )
) AS HC_S
ON  HC_S.MEMBER_CODE = HC.MEMBER_CODE
WHERE
    MAX(HC.CHECKUP_DATE)
ORDER BY
    CHG_WT DESC
,   CODE DESC