Archives of the TeradataForum

Message Posted: Fri, 26 Aug 2005 @ 15:49:09 GMT

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

Sergio:

Since there is no "tie-breaker" specified in the RANK function as second ORDER BY column, you are correct in that if two rows have the same job value, they will be ranked the same. However, DISTINCT is a faster operation than RANK and we were not told the number of potential duplicates. So, my thought was to get rid of the dups first and then RANK only what was left makes it more efficient due to fewer columns on which to check dups (job vs job and RANK) and uses less SPOOL. To take this discussion to the next level, if there are a large percentage of dups in the original data, using GROUP BY instead of DISTINCT would make it faster still.

Either way, it is a broom that will sweep the room and should accomplish the desired result. That's the rest of the story.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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