Trying to get correct rank order

Database
Enthusiast

Trying to get correct rank order

Hello -

I have the following subquery that I'm looking to rank correctly. I'm trying group or rank by Status then by cov_eff_dt(oldest). So ideally status would rank one unless there is no status in which case date would rank 1 then blank status 2.

--Ranking query to  rank active over termed and only take termed if no active.
create volatile table MinCovEffDts as(
sel indiv_agrmt_id
,c_typ_cov
,Status
,cov_eff_dt
,RANK () OVER (PARTITION BY
indiv_agrmt_id
,c_typ_cov
ORDER BY Status, cov_eff_dt) AS RankVal
from CovEffDts
group by indiv_agrmt_id
,c_typ_cov
,Status
,cov_eff_dt
where indiv_agrmt_id in ('LAC657150','LNC620450')
) with data
PRIMARY INDEX (indiv_agrmt_id)
on commit preserve rows

1 REPLY
Senior Supporter

Re: Trying to get correct rank order

I am not sure that I understand your requirement corrently. Can you give us some examples data with your expected results?

Why do you group by here? Shouldn't be needed...