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)
6 REPLIES
Enthusiast

Re: Rank with Qualify not giving the exact results.

select FROM_NAME,FROM_REVISION, LAST_UPDATE_DATE  from GEEDW_PLM_ODS_BULK_V.CDR_ODS_R_GE_OBJ_HST CDR_ODS_R_GE_OBJ_HST

0024                301345498360631                       1/24/2014 11:22:17

0024               431365606243002                       12/16/2013 20:16:44

0024              491333037555534                        6/6/2013 18:08:51

If you can see for part name 0024 has underwent three revisions and 3 upadted date.

I just want the 2 latest values i.e the first two.

Enthusiast

Re: Rank with Qualify not giving the exact results.

To clarify the question further, this is the result
part_name Revision last_updated date
xxx 04 02 Aug
xxx 04 01 aug
xxx 04 28 July
xxx 03 20 july
xxx - 15 july
xxx 02 10 july
yyy -A 13 aug
yyy -A 10 aug
yyy 04 05 aug
I want the output as:(latest two revisions)

xxx 04 2 Aug
xxx 03 20 july
yyy -A 13 aug
yyy 04 05 aug

hope its clear now..

please help me with the query.
Enthusiast

Re: Rank with Qualify not giving the exact results.

Hi Amit,

Based on above data i could able to think of below query.

Please try this and let me know if does not work or useful for you :

Select Sub.part_name

,Sub.Revision

,Sub.last_updated_date

FROM

( Select part_name

,Revision

,last_updated_date

FROM Table

QUALIFY ROW_NUMBER() OVER (PARTITION BY part_name,Revision ORDER BY last_updated_date DESC ) = 1 -- Latest updates for each Revision

) Sub

QUALIFY RANK() OVER (ORDER BY Sub.Revision DESC ) <= 2 -- Latest 2 Revisions

Thanks

Nagendra

Enthusiast

Re: Rank with Qualify not giving the exact results.

Hi Nagendra,

Thanks a lot for your quick reply. I ran the query which you sent, but its giving only 24  records. Ideally it should give atleast 5M records as there are 5M distinct parts. Also the REVISION column is coming as 'Y' , i dont know from where its coming.


OUTPUT:

NAME REVISION LAST_UPDATE_DATE

1 PQR200056 Y 7/12/2013 05:21:41

2 173B1730 Y 6/6/2013 13:48:34

3 131E1841 Y 6/6/2013 18:49:45

4 249A6743 Y 6/6/2013 19:06:18

5 D50A152 Y 10/21/2013 10:26:03

6 GEH-6721_VOL_III Y 7/15/2014 15:07:53

7 GEH-6421_VOL_I Y 7/15/2014 15:07:53

8 GEH-6721_VOL_I Y 7/15/2014 15:07:53

9 113A6316 Y 6/23/2014 03:17:45

10 114E5656 Y 11/11/2013 10:44:30

11 5546801 Y 6/6/2013 19:28:39

12 204D4109 Y 9/19/2013 10:25:03

13 227D2248 Y 3/17/2014 06:19:14

14 R-0430639 Y 3/17/2014 06:19:14

15 9041818 Y 5/1/2014 14:27:11

16 114E5657 Y 3/6/2014 09:27:55

17 348A5633 Y 6/27/2013 17:24:37

18 354A1513 Y 7/22/2014 08:09:54

19 172C9039 Y 6/6/2013 18:28:30

20 GEH-6421_VOL_II Y 7/15/2014 15:07:53

21 R-0366171 Y 10/17/2013 11:36:55

22 198D1611 Y 2/26/2014 11:22:57

23 GEH-6721_VOL_II Y 7/15/2014 15:07:53

24 GE-114A3759 Y 6/6/2013 17:02:55

Enthusiast

Re: Rank with Qualify not giving the exact results.

Below is the query i ran:

Select Sub.NAME

,Sub.REVISION

,Sub.last_update_date

FROM

( Select

 --CDR_ODS_R_GE_OBJ_HST .FROM_ID as FROM_ID,

CDR_ODS_R_GE_OBJ_HST .FROM_NAME as NAME ,

CDR_ODS_R_GE_OBJ_HST .FROM_REVISION as REVISION

,CDR_ODS_R_GE_OBJ_HST.LAST_UPDATE_DATE as LAST_UPDATE_DATE

FROM GEEDW_PLM_ODS_BULK_V.CDR_ODS_R_GE_OBJ_HST

QUALIFY ROW_NUMBER() OVER (PARTITION BY NAME,REVISION ORDER BY last_update_date DESC ) = 1 -- Latest updates for each Revision

) Sub

QUALIFY RANK() OVER (ORDER BY Sub.REVISION DESC ) <= 2-- Latest 2 Revisions

Enthusiast

Re: Rank with Qualify not giving the exact results.

Hi Amit,

Try with below query :

1)

Select Sub.NAME

,Sub.REVISION

,Sub.last_update_date

FROM

( Select

 --CDR_ODS_R_GE_OBJ_HST .FROM_ID as FROM_ID,

CDR_ODS_R_GE_OBJ_HST .FROM_NAME as NAME ,

CDR_ODS_R_GE_OBJ_HST .FROM_REVISION as REVISION

,CDR_ODS_R_GE_OBJ_HST.LAST_UPDATE_DATE as LAST_UPDATE_DATE

FROM GEEDW_PLM_ODS_BULK_V.CDR_ODS_R_GE_OBJ_HST

QUALIFY ROW_NUMBER() OVER (PARTITION BY NAME,REVISION ORDER BY last_update_date DESC ) = 1 -- Latest update for each Revision

) Sub

QUALIFY RANK() OVER (PARTITION BY Sub.NAME ORDER BY Sub.last_update_date DESC ) <= 2-- Last 2 Revisions

Thanks

Nagendra