Archives of the TeradataForum
Message Posted: Mon, 09 Oct 2000 @ 17:27:13 GMT
Hi Jim, this is actually quite a good use of the rank function. However, were you aware that rank won't doesn't guarantee a unique value.
For example if you had a table with the the following values
c1 c2 1 a 2 b 3 b 4 d select c1, c2, rank(c2 asc) from test; yields 1 a 1 2 b 2 3 b 2 4 d 4
This occurs because there is a tie in the values being ranked. If you want a guarantee that you get unique values, a better approach is to use csum as in the following query
select c1, c2, csum(1, c2 asc) from test; Which yields: 1 a 1 3 b 2 2 b 3 4 d 4
Now I've got to do a sales pitch. If you attend the Advanced SQL training courses, you should also learn a way with a single query you can add new unique ID's to an existing table within another key value even when no previous entries existed within that key value. For example, if you wanted to generate ID's that were unique ID's within some other value (eg. a department ID). The query even works if there were no previous entries within that department. The query in the course uses quite a few useful advanced techniques all in one query, outer joins, csum and correlated sub queries - at least it does if they used what I provided them.
Now if I could just figure out how to get commissions on course enrolments :-)
Hope this helps
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|