Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 11 Mar 2009 @ 18:03:30 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Ranking function
 
From:   Pararha, Shubhangi (S.)

Hi All,

Sometime back I posted the question regarding the missing ranks I was getting...

My rank function was something like this:

     ,RANK () OVER (PARTITION BY A.CODE,
                                 A.DATE,
                                 A.TRK_CD,
                                 A.CELL_CD,
                                 A.VALUE_CD,
                                 A.TREATY__CD,
                                 A.NUM_R,
                                 A.CNTRY,
                                 A.IND,
                                 A.CYCLE

     ORDER BY B.ACQS_DATE,B.NUM_R2)

I was seeing ranks being skipped... like after rank =1, rank=13...

I finally found the answer to that....

The reason was that, the table to which data was being loaded was a SET table.

The records that were being generated had many duplicates, because of joins between 3-4 tables.

We were not seeing these duplicates, as the result from the join was being ranked and loaded in the SET table in the same query.

So, say there are 12 duplicate records for a set of values, all will be ranked 1, And the next rank for different set of values would be 13. But while being loaded in SET table, the duplicates will be ignored.

What I saw in the final table was rank =1 and then rank= 13...

Anyways...thanks for all your answers.

Just wanted to share the info...

Sorry for the late post, recently I had a chance to open the code and investigate.

Last time, we had just stopped working on this stuff...


Regards,

Shubhangi Pararha



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016