issue with QUALIFY RANK

Question

Trying to delete records using below query.

DELETE FROM TABLE_1 WHERE NUMB IN 
(
SELECT NUMB FROM TABLE_2 
WHERE FLAG='Y' 
QUALIFY DENSE_RANK() OVER (ORDER BY LOAD_TIMESTAMP DESC )=1
)

Looks like issue with QUALIFY in sub-query. can you please advise alternate solution ?

Answer 1

can you please advise alternate solution ?

This query uses nesting rather than QUALIFY:

DELETE FROM TABLE_1 WHERE NUMB IN 
(
  SELECT NUMB 
  FROM (
    SELECT NUMB, DENSE_RANK() OVER (ORDER BY LOAD_TIMESTAMP DESC ) AS rn
    FROM TABLE_2 
    WHERE FLAG='Y'
  ) AS s
  WHERE rn = 1
)