Row_Number with Teradata GROUPBY

Question

I am trying to determine how I can use the ROW_NUMBER() function with TERADATA using the result set below. Here is what i have tried:

select col1, col2, ROW_NUMBER() OVER (ORDER by col2  ) 
    FROM
     (select col1, col2 
    from TABLE
    ORDER BY col2) A

Result Set

38  11/14/2016  1
38  11/15/2016  2
38  11/16/2016  3
38  11/17/2016  4
38  11/18/2016  5
38  11/19/2016  6
39  11/20/2016  7
39  11/21/2016  8
39  11/22/2016  9
39  11/23/2016  10
39  11/24/2016  11
39  11/25/2016  12

Expected:

Col1    Col2    Col3
38  11/14/2016  1
38  11/15/2016  1
38  11/16/2016  1
38  11/17/2016  1
38  11/18/2016  1
38  11/19/2016  1
39  11/20/2016  2
39  11/21/2016  2
39  11/22/2016  2
39  11/23/2016  2
39  11/24/2016  2
39  11/25/2016  2

Answer 1

You need to add PARTITION BY:

SELECT col1, 
       col2,
       ROW_NUMBER() OVER (PARTITION BY col1 ORDER by col2) AS row_index 
FROM TABLE
ORDER BY col2

Well, it seems like you actually need:

SELECT col1, 
       col2,
       DENSE_RANK() OVER (ORDER by col1) AS row_index 
FROM TABLE
ORDER BY col2