Counting how many times a value appears in a column and assigning an incremental number to each one.

Question

Counting how many times a value appears in a column and assigning an incremental number to each one.

Viewing 9 posts - 16 through 23 (of 23 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Ed Wagner (8/20/2014)
Eirikur Eiriksson (8/20/2014)
Quick question, what SQL Server Version are you on (SELECT @@VERSION)?
😎
SQL 2008 SP3 64-bit Standard Edition running on Windows Server 2008 Enterprise.

Then Lynn's solution is your best option.

😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

Eirikur Eiriksson (8/20/2014)
Ed Wagner (8/20/2014)
Eirikur Eiriksson (8/20/2014)
Quick question, what SQL Server Version are you on (SELECT @@VERSION)?
😎
SQL 2008 SP3 64-bit Standard Edition running on Windows Server 2008 Enterprise.
Then Lynn's solution is your best option.
😎

Definitely. It wasn't my OP and I posted a CTE solution. The cool part is that I learned something from Lynn posting the COUNT(*) OVER() syntax, which I'd never seen before. I looked at, tried it, looked it up on MSDN and saw it wasn't there for 2008. Hmmm. Anyway, it's cool that we can use it in 2008.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Ed Wagner (8/20/2014)
This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.
Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.

Actually, it also works in SQL Server 2005. That's where I first learned about using COUNT(*) OVER.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis (8/20/2014)
Ed Wagner (8/20/2014)
This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.
Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.
Actually, it also works in SQL Server 2005. That's where I first learned about using COUNT(*) OVER.

Check out Example B (and this is for SQL Server 2005):

http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Lynn Pettis (8/20/2014)
Lynn Pettis (8/20/2014)
Ed Wagner (8/20/2014)
This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.
Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.
Actually, it also works in SQL Server 2005. That's where I first learned about using COUNT(*) OVER.
Check out Example B (and this is for SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx

And yet the COUNT function: http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx.

It includes the OVER clause for 2014, but not for the others. I guess it's just where you look. Thanks, Lynn. I always appreciate learning something that I'll likely use frequently. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Ed Wagner (8/20/2014)
Lynn Pettis (8/20/2014)
Lynn Pettis (8/20/2014)
Ed Wagner (8/20/2014)
This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.
Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.
Actually, it also works in SQL Server 2005. That's where I first learned about using COUNT(*) OVER.
Check out Example B (and this is for SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx
And yet the COUNT function: http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx.
It includes the OVER clause for 2014, but not for the others. I guess it's just where you look. Thanks, Lynn. I always appreciate learning something that I'll likely use frequently. 😉

Not sure how I stumbled on to this since it was so long ago. I think I may have just been clinking hyperlinks in BOL and found it.

hamptonmom2009 Mr or Mrs. 500 Points: 574 More actions · Answer 7

Thank you very much for your responses!

This was a great help. I am a VBA girl and I have been given the opportunity to learn SQL for some projects I'm working on.

It's great to have a place to ask questions and learn new things!

djj SSCoach Points: 18831 More actions · Answer 8

Lynn Pettis (8/20/2014)
Not sure how I stumbled on to this since it was so long ago. I think I may have just been clinking hyperlinks in BOL and found it.

I have found more BOL's without good links than with. 😀

By-the-way thanks for the information.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

Ed Wagner (8/20/2014)
Lynn Pettis (8/20/2014)
Lynn Pettis (8/20/2014)
Ed Wagner (8/20/2014)
This is one of the great things about this site. A question gets posted and several working solutions get posted. Then we can all learn something from the different ways.
Lynn, I learned about the COUNT(*) OVER from your post. I tried it and it worked, so I looked it up on MSDN at http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx. The 2008 and 2012 pages don't have the OVER clause at all, but the 2014 does. It does work in SQL 2008 SP3. Thanks for posting it because I learned something new, which is always a great way to start the day.
Actually, it also works in SQL Server 2005. That's where I first learned about using COUNT(*) OVER.
Check out Example B (and this is for SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx
And yet the COUNT function: http://msdn.microsoft.com/en-us/library/ms175997%28v=sql.100%29.aspx.
It includes the OVER clause for 2014, but not for the others. I guess it's just where you look. Thanks, Lynn. I always appreciate learning something that I'll likely use frequently. 😉

Actually, the OVER clause has the same example for all versions since 2005. The aggregate functions' articles were changed for 2014 but lack the OVER clause in previous versions.

I'm sure I got there from the ranking functions article.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2