SELECT CompanyName,
ROW_NUMBER() OVER
                    (
                        PARTITION BY O.CompanyName
                        ORDER BY
                                 CASE WHEN AddressLine1 IS NOT NULL THEN AddressLine1 ELSE "ZZZZZZZZ" END ASC
                    ) AS BestDataForCompany
FROM CompanyData