Using SQL Server 2008 or 2012, I would like to generate "page numbers" in a resultset, based on changes in a single column. Below is an example resultset:
`
| Dept · · · | Person | Sales· | Page |
|---|---|---|---|
| Shoes· · · | Mike · | · 12 · | · 1 · |
| Shoes· · · | Karen· | · 18 · | · 1 · |
| Casual · · | Bill · | · 10 · | · 2 · |
| Suits· · · | Bill · | · 17 · | · 3 · |
| Accessories | Wendy· | · 15 · | · 4 · |
| Accessories | Jack · | · 15 · | · 4 · |
`
As an example, the above shows total sales, grouped by "Dept" and sales "Person". I want to output each Dept's results on a different page, and know which results go on which page, starting from 1. So I need to know how to generate the "Page" number column. It must start from 1 and increment each time the value of "Dept" changes down the resultset.
I've tried fiddling with ROW_NUMBER() but can't work out how to make row numbering only change when a column value changes... maybe that's not the right function to use or I don't understand it properly. How is this kind of "grouped row numbering" done?
Many thanks!!