Archives of the TeradataForum
Message Posted: Mon, 09 Oct 2000 @ 15:07:47 GMT
| Subj: || || Re: V2R3 SQL Documentation - Rank Function |
| From: || || Jim Downey |
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)