SQLTeam.com | Weblogs | Forums

How to generate a "page number" in a result set?


#1

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!!


#2
DENSE_RANK()

#3

Ah, didn't know about that one! Thanks so much!