ROW_NUMBER() Query example

SELECT
    tb_organization_csv.organizationid,
    tb_organization_csv.organizationparentid,
    tb_organization_csv.controlflag,
    tb_organization_csv.hierarchey_lvl,
    tb_organization_csv.parentdn,
    org.orgextendedname,
    org.orgnonlatinextendedname,
    org.orgstandardlegalextendedname,
    org.orgdeleteind,
    org.orgdunsuid,
    org.domesticultimatedunsuid,
    org.domesticultimateorgid,
    org.globalultimatedunsuid,
    org.globalultimateorgid,
    org.industryverticalsegmentcode,
    org.industrysegmentname,
    org.industryverticalcode,
    org.industryverticalname,
    pantr_addr.vatnationalidno AS vatid
FROM
    tb_organization_csv,
    tb_mdm_organization org,
    (
        SELECT
            organizationid,
            vatnationalidno
        FROM
            (
                SELECT
                    organizationid,
                    vatnationalidno,
                    ROW_NUMBER() OVER(PARTITION BY
                        organizationid
                        ORDER BY
                            organizationid
                    ) rn
                FROM
                    tb_mdm_partner_address
                WHERE
                    orgnationalidtypecode = 00018
            )
        WHERE
            rn = 1
    ) pantr_addr
WHERE
        controlflag = 1
    AND
        tb_organization_csv.organizationid = org.organizationid
    AND
        org.organizationid = pantr_addr.organizationid (+)
ORDER BY tb_organization_csv.hierarchey_lvl ASC;