Add number to per item - 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

Add number to per item

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2011-05-13 : 16:09:47

Example Table

A B
1 Joe@email
2 Joe@email
3 Joe@email
1 Smith@email
2 Smith@email
1 Jane@email
1 Kyle@email
2 Kyle@email

Basically, I have a table column B. I would like to insert a number in COL A for each email. So Joe's emails shows up 3 times so I have 1-3. Smith@email has 2 emails so I have 2 counts in COL A. Jane@email has 1 so it just has a value of 1. How do I populate this information in Col A based on the list from Col B?

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-05-13 : 16:58:27

Try this:

declare @t table (email varchar(20))
insert @t (email)
select 'Joe@email' union all
select 'Joe@email' union all
select 'Joe@email' union all
select 'Smith@email' union all
select 'Smith@email' union all
select 'Jane@email' union all
select 'Kyle@email' union all
select 'Kyle@email'

select row_number() over(partition by email order by email) as seq, *
from @t

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources