All Forums Database
bhartiya007 29 posts Joined 03/14
02 Aug 2014
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

 

 

 

@Amit
dnoeth 4628 posts Joined 11/04
02 Aug 2014

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

Dieter

You must sign in to leave a comment.