January 5, 2011 at 7:27 pm
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! 🙁
January 5, 2011 at 8:11 pm
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
January 6, 2011 at 2:49 am
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/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply