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.
| Author |
Topic |
|
Humate
Posting Yak Master
101 Posts |
 Posted - 2008-01-15 : 08:53:46
|
| Hi all,I have been struggling with this query. I want to create a column that has a running count of how many times a reference number is populated in the results of my query. For instance:Reference---------------Counter1234 ---------------------- 11234 ---------------------- 21234 ---------------------- 34321 ---------------------- 12341 ---------------------- 12341 ---------------------- 2Any help much appreciated.Humate |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-01-15 : 09:06:42
|
| Make use RANK() function.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-01-15 : 09:10:42
|
| read about "PARTITION BY" and "ROW_NUMBER()"example herehttp://www.4guysfromrolla.com/webtech/010406-1.shtml#postadlink |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-15 : 09:14:19
|
| SELECT ROW_NUMBER() OVER (PARTITION BY Reference ORDER BY PK )AS Counter,*FROM Tablepk is your primary key |
|
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2008-01-15 : 11:27:08
|
| Excellent, thanks all! I have used Row_number () and Partition by before, but I couldn't remember how to do this at all! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Running count of non unique rows