Update the running numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update the running numbers

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-15 : 08:40:29

Hi,
I have a table Employee with the following structure and values.
------------------------------
Sno EmpId Qualification orderNo
-------------------------------
1 E001 BS
2 E001 MS
3 E001 PhD
4 E002 BCA
5 E003 BE
6 E003 ME
7 E004 Bcom
-------------------------------
I wanted to update the orderno column in sequence against the EmpId.

Expected Output
------------------------------
Sno EmpId Qualification orderNo
-------------------------------
1 E001 BS 1
2 E001 MS 2
3 E001 PhD 3
4 E002 BCA 1
5 E003 BE 1
6 E003 ME 2
7 E004 Bcom 1
-------------------------------

Thanks for your help.

Rgds
SSM.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-09-15 : 09:20:55

[code]
update e
set e.orderNo=Der.SerNum
from emp e
inner join (select Sno,row_number() over(partition by EmpId order by Sno ) as SerNum
from emp)der on e.Sno=der.Sno

[/code]

--Ranjit

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-15 : 09:23:40

Is there an orderno column in your table? Or do you just want to present the updated orderno as part of a select statement. If you're updating the table, you could do:

[CODE]
declare @employee table (sno int, empid varchar(4), qualification varchar(10), orderno int)
insert into @employee (sno, empid, qualification)
values (1, 'E001','BS'), (2, 'E001','MS'), (3, 'E001','PhD'), (4, 'E002','BCA'), (5, 'E003', 'BE'), (6, 'E003', 'ME'), (7, 'E004', 'Bcom')

;
with
mycte (sno, empid, qualification, orderno)
as (
select sno, empid, qualification, ROW_NUMBER() over (partition by empid order by sno) as orderno
from @employee

group by sno, empid, qualification)

update @employee
set orderno=mycte.orderno
from @employee e
join mycte on mycte.empid=e.empid and mycte.sno=e.sno and mycte.qualification=e.qualification

select * from @employee
[/CODE]

If just updating in a query, you just need the select statement embedded in the CTE:

[CODE]
select sno, empid, qualification, ROW_NUMBER() over (partition by empid order by sno) as orderno
from @employee

group by sno, empid, qualification
[/CODE]

edit: changed ROW_NUMBER order by to "sno"

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-18 : 03:59:32

Thanks @Ranjit, its working fine.
Thanks @flamblaster, since the first solution was fine, i didn't try with your solution.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-18 : 04:05:19

Sure...more or less the same thing...just a different way to approach.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 05:48:03

i dont think you need a join either. the below should suffice


update der 
set der.orderNo=der.SerNum
from (select orderNo,row_number() over(partition by EmpId order by Sno ) as SerNum
			from emp)der

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources