RowNumber() really is a great addition to SQL Server and has been very helpful in many of my applications. But I have been granted a new task, adding grouping within a sort. To explain the dilemma, let's look at some rows of data that are sorted in date order:
| RecID | GroupTag | SortColumn | RowNumber |
| 1 | NULL | 2009/2/13 | 1 |
| 2 | NULL | 2009/2/13 | 2 |
| 3 | GroupSet 2 | 2009/2/14 | 3 |
| 4 | NULL | 2009/2/14 | 4 |
| 5 | GroupSet 2 | 2009/3/01 | 5 |
| 6 | GroupSet 2 | 2009/3/10 | 6 |
| 7 | GroupSet 2 | 2009/3/10 | 7 |
What I need to do is sort it so that RecIDs 5-7 show up between 3 and 4, because they belong to a group (defined by the GroupTag) and should be grouped together with the first record in the sort of that group.
To make things slightly easier, I'm actually selecting a subset of data and using the RowNumber() function to put it in order and support paging. The trick is to resort to adding a column called GroupSort to the data to apply grouping within either a temporary table or table variable. Now for the tricky part (or at least the part I'd like to refactor away). If the GroupTag is null, I set the value of the GroupSort column equal to the RowNumber column. If GroupTag isn't, then I do an update that fills GroupSort with the minimum RowNumber for the GroupTag plus the current RowNumber/1000. This gives me the results seen below:
| RecID | GroupTag | SortColumn | RowNumber | GroupSort |
| 1 | NULL | 2009/2/13 | 1 | 1 |
| 2 | NULL | 2009/2/13 | 2 | 2 |
| 3 | GroupSet 2 | 2009/2/14 | 3 | 3.003 |
| 4 | NULL | 2009/2/14 | 4 | 4 |
| 5 | GroupSet 2 | 2009/3/01 | 5 | 3.005 |
| 6 | GroupSet 2 | 2009/3/10 | 6 | 3.006 |
| 7 | GroupSet 2 | 2009/3/10 | 7 | 3.007 |
When I finally output this data, it will correctly sort the RecIDs the way I want.
Inelegant, but it works. Share a better solution in the comments.