Counting rows one by one - 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

Counting rows one by one

Author

Topic

munya
Starting Member

6 Posts

Posted - 2012-07-25 : 09:00:58

Hello there,

Need some help on counting rows one by one in a table of
some identical values:
I want to update No_APPEARANCE on each row in a table
depending on IDno.
Example My table looks like this:

IDno NO_APPEARANCE
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
4
4
4
4
5
5
5
6
6
7
8
8
3

Please I need yr support

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-25 : 09:09:57

This?

declare @Sample table (id int, NoOcc int)
insert @Sample(id)
select 1 union all
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 3

select * from @Sample

update dt
set NoOcc=dt.rownum
from
(select id, NoOcc, row_number() over (partition by id order by id) as rownum from @Sample)dt

select * from @Sample

No, you're never too old to Yak'n'Roll if you're too young to die.

munya
Starting Member

6 Posts

Posted - 2012-07-25 : 09:29:05

Oh Man It worked thanks a lot. Can you please explain a bit to me how it works? Or have a link to read more on this concept?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-25 : 09:40:26

What in special do you want to know?
The use of a "derived table"?
The use of "row_number()"?
The use of alias names for columns and tables?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-25 : 09:55:27

I'd say "Books Online".

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:19:00

quote:
Originally posted by munya

Oh Man It worked thanks a lot. Can you please explain a bit to me how it works? Or have a link to read more on this concept?

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources