Add A Row to Count the Occurrence of Duplicates

Question

Add A Row to Count the Occurrence of Duplicates

photonicman

Right there with Babe

Points: 736
More actions
January 5, 2011 at 7:27 pm

#230133

Okay, how about adding a column to this data to count the first, second, third, etc occurrence of duplicate PA_EnterpriseID's in column 1 - Placing the count in a new column:
PA_EnterpriseIDPP_NamePP_ID, *Occurrence*
91st Quarter 2010 Remove from PIP Agency Pruning132, 1
93rd Quarter 2009 PIP Agency Pruning112, 2
92nd Quarter 2009 PIP Agency Pruning104, 3
91st Quarter 2009 PIP Agency Pruning 102, 4
93rd Quarter 2008 PIP Agency Pruning98, 5
10Bonus Commission Plan (BCP) 200883, 1
10College World Series Sales Contest 200869, 2
10Bainwest63, 3
10Bonus Commission Plan (BCP) 200761, 4
10NASCAR 200755, 5
10Seminars - 200751, 6
Please - Someone help me! 🙁

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Check out this article on SQL Server Ranking Functions[/url]. It looks like the ROW_NUMBER() function will do what you're after.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

psingla Hall of Fame Points: 3840 More actions · Answer 2

photonicman (1/5/2011)
Okay, how about adding a column to this data to count the first, second, third, etc occurrence of duplicate PA_EnterpriseID's in column 1 - Placing the count in a new column:
PA_EnterpriseIDPP_NamePP_ID, *Occurrence*
91st Quarter 2010 Remove from PIP Agency Pruning132, 1
93rd Quarter 2009 PIP Agency Pruning112, 2
92nd Quarter 2009 PIP Agency Pruning104, 3
91st Quarter 2009 PIP Agency Pruning 102, 4
93rd Quarter 2008 PIP Agency Pruning98, 5
10Bonus Commission Plan (BCP) 200883, 1
10College World Series Sales Contest 200869, 2
10Bainwest63, 3
10Bonus Commission Plan (BCP) 200761, 4
10NASCAR 200755, 5
10Seminars - 200751, 6
Please - Someone help me! 🙁

Try it out

select *,row_number() over (partition by PA_EnterpriseID order by PA_EnterpriseID) from tablename

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/