您似乎想移动
CASE
表达式
ORDER BY
子句(而不是有不同的
lifecycle_type
s在不同分区中):
SELECT *
FROM (
SELECT NORM_VERSION,
NORM_FULL_VERSION,
NORM_PRODUCT_VALUE,
LIFECYCLE_TYPE,
START_DATE,
RANK() OVER (
PARTITION BY
NORM_VERSION,
NORM_FULL_VERSION,
NORM_PRODUCT_VALUE
ORDER BY
CASE WHEN LIFECYCLE_TYPE ='internal' THEN 1 ELSE 2 END,
SYS_UPDATED_ON DESC
) as dest_rank
FROM LIFECYCLE
)
WHERE dest_rank = 1 ;