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
You appear to want to move the CASE
expression to the ORDER BY
clause (rather than having different lifecycle_type
s 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 ;