Archives of the TeradataForum

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

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

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