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 |
|
bi89405
Starting Member
35 Posts |
 Posted - 2012-01-11 : 09:33:37
|
| Hello SQL Experts,I have the following piece of data.ID1 2ID1 2ID1 2ID1 2ID2 3ID2 3ID2 3ID2 3I need to produce the following right-most column below.ID1 2 1ID1 2 1ID1 2 2ID1 2 2ID2 3 1ID2 3 1ID2 3 1ID2 3 2Essentially, the center column represents the maximum # of items that can print on a page. The right-most column needs to be created to identify which row(s) will print for each page. So, in this example, the first 2 items will print on page 1 and the next 2 items will print on page 2. What I basically need is a counter that looks at the center column (page-limit field) and starts assigning 1 to each row until it reachs the page-limit field, then it increments by 1 and continues the running total until it sees a change in the left-most field (ID field).Can anyone provide an elegant solution?TIA,Zack |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-11 : 10:08:36
|
Would this work for you?SELECT *, (ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1)-1)/col2+1 as col3FROM yourTable |
 |
|
|
bi89405
Starting Member
35 Posts |
Posted - 2012-01-11 : 11:07:47
|
| sunitabeck,Thank you! I spent literally 5 minutes marveling at the SQL you produced so quickly.Nicely done! Thanks again!Zack |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-11 : 12:10:37
|
| You are very welcome, and thank you for the kind words! |
|
|
|
|
|
|
Running counter field