Archives of the TeradataForum

Message Posted: Mon, 09 Oct 2000 @ 15:07:47 GMT

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

We found the RANK function to be of great use to us although I don't think we are using it for its intended purpose. Other DBMS software have the ability to generate unique ids as it loads rows of data. We had an application that needed this same functionality. We had been doing this as a part of our mainframe ETL process but changed it to run at load time uisng the RANK function.

We use RANK and all columns that make a row unique to assign a number from 1:n on a candidate or input table that contains data we intend to merge with existing data. At the same time we find the max(n) in the existing data. We then insert the new data into the old table after incrementing the rank value (n = n(new)+nmax(old)) so that the range is above that of the existing data.

For example, if we have 10 new rows, RANK would assign them the values 1-10 If the exisitng table had 100 rows they would be numbered 1- 100. Note that the max value is 100. When we add the new rows they become 101-110. (100+1...100+110)

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