Query to find State and State Code
SELECT hg.geography_code state_code,
hgi.identifier_value state_name
FROM (SELECT gi.geography_id,
gi.identifier_value,
ROW_NUMBER() OVER (
PARTITION BY gi.geography_id
ORDER BY gi.last_update_date DESC) rn
FROM HZ_GEOGRAPHY_IDENTIFIERS gi
WHERE gi.identifier_type = 'NAME'
AND gi.identifier_subtype = 'STANDARD_NAME'
AND gi.geography_type = 'STATE'
AND gi.language_code = 'US'
AND gi.primary_flag = 'N') hgi
JOIN HZ_GEOGRAPHIES hg
ON hg.geography_id = hgi.geography_id
WHERE hg.geography_type = 'STATE'
AND hgi.rn = 1
ORDER BY hgi.identifier_value;
