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

Question

5.00/5 (1 vote)

See more:

Posted 20-Jul-15 4:40am

Bittu14

Updated 20-Jul-15 5:28am

v5

Add a Solution

Comments

Wendelius 20-Jul-15 11:06am

First of all, how the data should be sorted? Why Arijit has sequence number 1 and Suvayu has 4?
Also where did the 456 disappear from Arijit and 300 from Priyam?

Bittu14 20-Jul-15 11:13am

Data has stored based on nothing. Means first I have insert arijit, suman like this. If you can insert the data like arijit, priyam, suvayu, that doesn't matter.
id is primary key. i have entered worng data. sorry for that. i have changed it. check once.

Wendelius 20-Jul-15 11:21am

Still doesn't make sense. If Id is the primary key, why Arijit has the value 120 two times? Suman has 121 while that value didn't exist in the original data...

How do you calculate the new Id's?

Bittu14 20-Jul-15 11:29am

I have used some constraints over there, so that it has stared from 120.
Yes i understand. Once again sorry.

Wendelius 20-Jul-15 11:33am

Okay, new the Id's make sense. How do you calculate the sequence number? For example Priyam why he/she is having 3 while Suvayu has 4. What's the logic behind the number?

Bittu14 20-Jul-15 11:34am

I have tried this below query. but it has not work.
UPDATE INFO_T
SET SEQ_NO = (SELECT ROW_NUMBER() OVER(ORDER BY NAME)
FROM INFO_T)
WHERE (SELECT DISTINCT NAME FROM INFO_T);

Bittu14 20-Jul-15 11:36am

The logic behind the number is nothing. It has come as per the data sequence.

Bittu14 20-Jul-15 11:43am

2 solutions

Solution 2

Here is a query to get the results in the order you have specified.
And also update the variable table @INFO_T with the desired result.
From the CTE format, you should be able to see how the data is being manipulated.

SQL

--setup test data
declare @INFO_T table (Id int, Name varchar(100), Seq_no int);
insert into @INFO_T (Name, Id)
	select 
		'Arijit' Name,
		120 Id
	union select 'Suman', 121
	union select 'Priyam', 122
	union select 'Arijit', 123
	union select 'Suvayu', 124
	union select 'Priyam', 125
;

--update OrigData with FinalData
with GroupNames as ( --Group the Names
	select 
	Name,
	Id,
	row_number() over (partition by name order by id) as rowid
	from @INFO_T OrigData
), NameSequence as ( --Get Squence of Names via Id
	select 
		Name,
		row_number() over (order by id) as Seq_No
	from GroupNames
	where rowid = 1
)
update FinalData
set FinalData.Seq_No = NameSequence.Seq_No
from @INFO_T OrigData
inner join NameSequence
	on OrigData.name = NameSequence.name
inner join @INFO_T FinalData
	on OrigData.Id = FinalData.Id
;

--final result
select Name, Id, Seq_no 
from @INFO_T 
order by Id;

Hope that helps out.

Posted 20-Jul-15 6:34am

jaket-cp

Updated 21-Jul-15 0:27am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-20T05:53:00

Solution 1

Not sure if I still understood the question correctly, but does this kind of query return the correct results?

SQL

create table test21  (
   name varchar(100),
   id   int
);

insert into test21 values ('Arijit', 120);
insert into test21 values ('Suman', 121);
insert into test21 values ('Priyam', 122);
insert into test21 values ('Arijit', 123);
insert into test21 values ('Suvayu', 124);
insert into test21 values ('Priyam', 125);

select t1.name,
       t1.id,
	   t2.seq_no
from test21 t1,
     (select t3.name,  row_number() over (order by t3.name) as seq_no
	  from (select distinct name 
	        from test21) t3
	  ) t2
where t2.name = t1.name
order by t1.id;

Posted 20-Jul-15 5:53am

Wendelius

Comments

Bittu14 20-Jul-15 14:29pm

I want the update code. When I have tried this then some error occurred.

Wendelius 20-Jul-15 14:35pm

First you should work out the select to run. After that it should be quite simple to modify it to update

What is the error you got?

Bittu14 20-Jul-15 16:06pm

update test21 set SEQ_NUM =(select
t2.seq_no
from test21 t1,
(select t3.name, row_number() over (order by t3.name) as seq_no
from (select distinct name
from test21) t3
) t2
where t2.name = t1.name
order by t1.id)

I've tried the above query but now I am getting the below error

"ORA-00907: missing right parenthesis" can you please suggest what I am doing wrong here?

Bittu14 20-Jul-15 16:10pm

select
t2.seq_no
from test21 t1,
(select t3.name, row_number() over (order by t3.name) as seq_no
from (select distinct name
from test21) t3
) t2
where t2.name = t1.name
order by t1.id

this query has given the exact answer but when ever i have tried to update then only i have faced the problem. Please help me out..

Wendelius 21-Jul-15 1:03am

At least one problem is that the sub-query is fetching values from all rows at the same time. This causes an error since a column can only have a single value. In order to correct this you need a correlation between the row to be updated and the data fetched.

Taken that the select query is correct, try something like:
UPDATE test21
SET SEQ_NUM = a.seq_no
FROM (SELECT t1.id, t2.seq_no
FROM test21 t1,
(SELECT t3.name, ROW_NUMBER() OVER (ORDER BY t3.name) AS seq_no
FROM (SELECT DISTINCT name
FROM test21) t3
) t2
WHERE t2.name = t1.name ) a
WHERE a.id = test21.id;

Bittu14 21-Jul-15 9:10am

Thank you so much..

Wendelius 22-Jul-15 1:27am

You're welcome :)