您似乎希望将CASE表达式移动到ORDER BY子句中（而不是在不同的分区中使用不同的lifecycle_type）：

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 ;