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_EnterpriseID PP_Name PP_ID, *Occurrence*

  9 1st Quarter 2010 Remove from PIP Agency Pruning 132, 1

  9 3rd Quarter 2009 PIP Agency Pruning 112, 2

  9 2nd Quarter 2009 PIP Agency Pruning 104, 3

  9 1st Quarter 2009 PIP Agency Pruning 102, 4

  9 3rd Quarter 2008 PIP Agency Pruning 98, 5

  10 Bonus Commission Plan (BCP) 2008 83, 1

  10 College World Series Sales Contest 2008 69, 2

  10 Bainwest 63, 3

  10 Bonus Commission Plan (BCP) 2007 61, 4

  10 NASCAR 2007 55, 5

  10 Seminars - 2007 51, 6

  Please - Someone help me! 🙁

• WayneS

  SSC Guru

  Points: 95405

  More actions

  January 5, 2011 at 8:11 pm

  #1269847

  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

  January 6, 2011 at 2:49 am

  #1269946

  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_EnterpriseID PP_Name PP_ID, *Occurrence*

  9 1st Quarter 2010 Remove from PIP Agency Pruning 132, 1

  9 3rd Quarter 2009 PIP Agency Pruning 112, 2

  9 2nd Quarter 2009 PIP Agency Pruning 104, 3

  9 1st Quarter 2009 PIP Agency Pruning 102, 4

  9 3rd Quarter 2008 PIP Agency Pruning 98, 5

  10 Bonus Commission Plan (BCP) 2008 83, 1

  10 College World Series Sales Contest 2008 69, 2

  10 Bainwest 63, 3

  10 Bonus Commission Plan (BCP) 2007 61, 4

  10 NASCAR 2007 55, 5

  10 Seminars - 2007 51, 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/