Archives of the TeradataForum

Message Posted: Mon, 09 Oct 2000 @ 17:27:13 GMT

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

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

Glenn McCall
NCR, CRM Division
San Diego

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