Archives of the TeradataForum

Message Posted: Wed, 14 Jun 2006 @ 20:53:20 GMT

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

A bit confused on your requirements as the results set you have is bigger that your source rows in your example below

Do you want to just sort the table in col1, col2 sequence?

     sel col1,col2
     from  test
     order by 1,2

Or do you want to generate an incremental sequence number for each col2? Like this.

     select csum(1,col2),col2
     from  test
     order by 1,2

Now it also depends on if you don't care what the sequence number is against each value that is the same , e.g. all 200's get a unique number BUT there is no guarantee that each time you run the code then a different sequence number is allocated. If you want repeatable then the order columns in the CSUM need to identify a unique row. So if your col1 & col2 are unique then

     select csum(1,col1,col2),col2
     from  test
     order by 1,2

There are other functions like rank that will give you an individual sequence number BUT the same number for the same value

     So select rank(col2),col2
     from  test
     order by 1,2

Would give you

     1  10
     1  10
     3  20
     4  60
     4  60
     6  200
     6  200
     6  200
     ......

Again if col1, col2 were unique then

     So select rank(col2,col1),col2
     from  test
     order by 1,2

Would give you sequential numbering

John

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