How to rank based on certain column value oracle SQL

June 20, 2023

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,
                                   CASE WHEN LIFECYCLE_TYPE ='internal'
                                        then 1 end
                      ORDER BY SYS_UPDATED_ON DESC) as dest_rank
  FROM LIFECYCLE
)
where dest_rank = 1 ;

how to rank ,if lifecycle type is internal then rank it 1 then increment the ranking from the grouped columns in the above query.

from below screenshot I am trying to rank internal 1 then publisher 2

>Solution :

You appear to want to move the CASE expression to the ORDER BY clause (rather than having different lifecycle_types in different partitions):

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 ;