All Forums Database
gfuller40 24 posts Joined 05/10
04 Jan 2012
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
ulrich 816 posts Joined 09/09
04 Jan 2012

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...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.