CODEWITHSUNDEEP
sqloracle-databasewindow-functionsrow-number
user3040157
user3040157

Reputation: 85

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

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?

Upvotes: 0

Views: 2178

Answers (3)

TLaV
TLaV

Reputation: 389

This is MS SQL, but it looks like Row_Number functions the same in Oracle. If you can't do a CTE (WITH) statement, these could be done as sub-queries instead. Maybe someone with Oracle SQL experience could use this example and re-write it. I can't guarantee the results, since they are unordered, but basically I first create a RowID, without order, from the table. This allowed me to ensure the names were in the same order you have them in (which seems to be insert order). Then I essentially did sub-queries of that to get the FIRST distinct instance of each name in order. Then I joined back to the original table. Here it is in SQLFiddle.

;WITH NoOrderBy AS
(
  select Row_Number() OVER(ORDER BY (SELECT 0)) RowID,Name,id
  FROM MyTable
),
DistinctNames  AS
(
  SELECT Row_Number() OVER(ORDER BY RowID) RowID3,Name,RowID,id
  FROM
  (
    SELECT Row_Number() OVER(PARTITION BY NAME ORDER BY RowID) RowID2,Name,RowID,id
    FROM NoOrderBy
   ) a
   WHERE RowID2 = 1
)
SELECT mt.Name,di.id,di.RowID3 Seq_no 
FROM MyTable mt
INNER JOIN DistinctNames di
ON mt.Name =di.Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

SQL FIDDLE DEMO

SELECT Table1.name, Table1.ID, SEQ.Seq_no
FROM 
    (SELECT name, ROW_NUMBER() OVER(order by name) as Seq_no
     FROM (SELECT  DISTINCT name FROM Table1) as unique_name ) as SEQ INNER JOIN
     Table1 on SEQ.name = Table1.Name

Upvotes: 1

Related Questions