How i can change SQL in order to to avoid DISTINCT and 2 fields in WHERE?

here is my SQL, I want to get rid of WHERE (ADDRESS_KEY, PRIO). How I can say it with JOIN?

SELECT DISTINCT ADDRESS_KEY, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, CITY, COUNTRY_CD, IS_TO_BE_DELETED, STREET, ROW_UPDATE_DATE
FROM V_CUSTACCHOLD
WHERE (ADDRESS_KEY, PRIO) IN (SELECT DISTINCT ADDRESS_KEY, MIN(PRIO) FROM V_CUSTACCHOLD GROUP BY ADDRESS_KEY)

Thanks a lot in advance.

Answer

You can join the table to the query you use in the WHERE clause:

SELECT DISTINCT ADDRESS_KEY, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, CITY, COUNTRY_CD, IS_TO_BE_DELETED, STREET, ROW_UPDATE_DATE
FROM V_CUSTACCHOLD v1 
INNER JOIN (SELECT ADDRESS_KEY, MIN(PRIO) PRIO FROM V_CUSTACCHOLD GROUP BY ADDRESS_KEY) v2
ON v2.ADDRESS_KEY = v1.ADDRESS_KEY AND v2.PRIO = v1.PRIO

You may remove DISTINCT from SELECT depending on the results.