Rank with Qualify not giving the exact results.

Database
Enthusiast

Rank with Qualify not giving the exact results.

Hi Gurus,

I am stuck in an issue where i want the two latest values of a column(REVISION) based on the max(last_updated_date).

Scenario:

There is a Revison column coressponding to each part column( hardware part). Whenever there is any change in the part the revision gets changed and the last_updated date also changes.

Revision can be anything numbers, alphabets, - _ etc.

so suppose if i have 100 parts

and 60 parts donot change and 40 changes

so 40 will have atleast two latest revision.

so in total there would be

60+40*2=140 parts in the output.

--------------------------------------

Without using QUALIFY, I am getting more than 5M distinct parts. so atleast i should get 5M records( in case when no parts has underwent any revision)

SELECT DISTINCT FROM_NAME

FROM(

select

CDR_ODS_R_GE_OBJ_HST .FROM_ID as FROM_ID,

CDR_ODS_R_GE_OBJ_HST .FROM_NAME as FROM_NAME  ,

CDR_ODS_R_GE_OBJ_HST .FROM_REVISION as FROM_REVISION,

max(CDR_ODS_R_GE_OBJ_HST .LAST_UPDATE_DATE) as LAST_UPDATE_DATE

--RANK( )  OVER ( ORDER BY max(CDR_ODS_R_GE_OBJ_HST .LAST_UPDATE_DATE) DESC) AS RANK1

from GEEDW_PLM_ODS_BULK_V.CDR_ODS_R_GE_OBJ_HST CDR_ODS_R_GE_OBJ_HST

--WHERE CDR_ODS_R_GE_OBJ_HST.FROM_name='323A4747UUP15A'

--QUALIFY RANK1<=2

group by 1,2,3

) TM

14728.20721.304.13308(from_id)       R-0331128(from_name)                   -(revision)                     8/7/2013 20:30:02(last_updated date)

----

BUt while using qualify <=2 with rank getting only 186 parts.

select

CDR_ODS_R_GE_OBJ_HST .FROM_ID as FROM_ID,

CDR_ODS_R_GE_OBJ_HST .FROM_NAME as FROM_NAME  ,

CDR_ODS_R_GE_OBJ_HST .FROM_REVISION as FROM_REVISION,

max(CDR_ODS_R_GE_OBJ_HST .LAST_UPDATE_DATE) as LAST_UPDATE_DATE

,RANK( )  OVER ( ORDER BY max(CDR_ODS_R_GE_OBJ_HST .LAST_UPDATE_DATE) DESC) AS RANK1

from GEEDW_PLM_ODS_BULK_V.CDR_ODS_R_GE_OBJ_HST CDR_ODS_R_GE_OBJ_HST

--WHERE CDR_ODS_R_GE_OBJ_HST.FROM_name='323A4747UUP15A'

QUALIFY RANK1<=2

group by 1,2,3

Please help me with the query.

Regards,

Amit


Tags (1)
1 REPLY
Junior Contributor

Re: Rank with Qualify not giving the exact results.

Hi Amit,

you probably need to partition by your part column, PARTITION BY FROM_ID?