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(
,RANK () OVER (PARTITION BY
ORDER BY Status, cov_eff_dt) AS RankVal
group by indiv_agrmt_id
where indiv_agrmt_id in ('LAC657150','LNC620450')
) with data
PRIMARY INDEX (indiv_agrmt_id)
on commit preserve rows
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...