CODEWITHSUNDEEP
sqlteradatarankdense-rankteradatasql
team 9
team 9

Reputation: 5

issue with QUALIFY RANK

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 ?

Upvotes: 0

Views: 234

Answers (1)

SelVazi
SelVazi

Reputation: 16063

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
)

Upvotes: 1

Related Questions