SELECT
    P.system_id AS systemId,
    P.person_id AS personId,
    P.email AS email,
    P.employee_code AS employeeCode,
    P.bar_code AS barCode,
    P.rfid AS rfid,
    P.card_number AS cardNumber,
    P.first_name AS firstName,
    P.last_name AS lastName,
    P.primary_telephone AS primaryTelephone,
    P.address1 AS address1,
    P.address2 AS address2,
    P.city AS city,
    P.country AS country,
    P.state_province_region AS stateProvinceRegion,
    P.zip_or_postal_code AS zipOrPostalCode,
    IIF(A.membershipEnd IS NULL , 0, 1) AS deleted,
    P.is_blocked AS blocked,
    A.lastModified AS modifiedAt,
    P.created_at AS createdAt
FROM
    (
        SELECT
            P.person_id AS personId,
            GREATEST(P.modified_at, UGM.modified_at) AS lastModified,
            UGM.end_date  AS membershipEnd
        FROM
            user_group_membership UGM
                LEFT JOIN person P
                          ON UGM."member" = P.person_id
        WHERE
                UGM.user_group_membership_id IN (
                SELECT
                    MAX(UGM.user_group_membership_id)
                FROM
                    user_group_membership UGM
                        LEFT JOIN person P  ON UGM."member" = P.person_id
                WHERE
                        P.customer_id = :customerparam
                  AND UGM.customer_id = :customerparam
                  AND UGM.assigned_group_id = :groupparam
                  AND (UGM.modified_at >= TO_DATE(:update_after,'yyyy-mm-dd hh24:mi:ss')
                    OR P.modified_at >= TO_DATE(:update_after,'yyyy-mm-dd hh24:mi:ss'))
                GROUP  BY UGM."member")) A

        LEFT JOIN person P
                  ON A.personId = P.person_id
WHERE  lastmodified > TO_DATE(:update_after,'yyyy-mm-dd hh24:mi:ss')
   OR ( lastmodified = TO_DATE(:update_after,'yyyy-mm-dd hh24:mi:ss')
    AND personId > :person_id )
ORDER BY
    A.lastmodified ASC,
    A.personId ASC
OFFSET :offset ROWS
    FETCH NEXT :limit ROWS ONLY