How to update a column value with ROW_NUMBER() OVER value based on distinct values of a column

Question

I have one table, in which I want to update the value for a particular column. The column value should be row number. Based on a column distinct value I need to update the newly created column value.

Example:

Contents of INFO_T table:

Name   | id 
------------
Arijit | 120
Suman  | 240
Priyam | 555
Arijit | 456
Suvayu | 110
Priyam | 300

I need to add one more column within the above table INFO_T, below is the example. I have to use ROW_NUMBER() OVER function, through which I have to update that SEQ_NO column.

Name   | id  | Seq_no
------------------
Arijit | 120 | 1
Suman  | 240 | 2
Priyam | 555 | 3
Arijit | 120 | 1
Suvayu | 110 | 4
Priyam | 555 | 3

How to get that above result?

Answer 1

You can use dense_rank() for this purpose:

select name, id, dense_rank() over (order by minid) as seqno
from (select t.*, min(id) over (partition by name) as minid
      from table t
     ) t;

If you wanted to do this just with row_number():

select t.name, t.id, tt.seqnum
from table t join
     (select t.name, row_number() over (order by min(id)) as seqno
      from table t
      group by t.name
     ) tt
     on t.name = tt.name;

However, I don't know why you would want to do that.