I have a resultset in a temp table on SQL Server 2008. One of the fields is a group Id, and for each group I can have any number of rows. I'd like to get in a new column the position of the row in the group, such as "row 1 of 3" So, if I have temp table #Data(GroupId int, Item char) with
GroupId Item
-------------
5 C
5 A
5 T
2 U
3 Y
3 L
I'd like to get
GroupId Item Pos GroupCount
----------------------------
5 C 1 3
5 A 2 3
5 T 3 3
2 U 1 1
3 Y 1 2
3 L 1 2
I managed to do this doing
SELECT D.GroupId, D.Item, ROW_NUMBER() OVER (PARTITION BY D.GroupId ORDER BY D.Item) AS Pos, COUNT(1) AS GroupCount
FROM #Data D
INNER JOIN #Data DC
GROUP BY D.GroupId, D.Item
But I'm not too happy with this, because the query is actually a little more complex because of all the columns I actually have to retrieve, and because doing both an inner join with itself to get the group row count and a partition to get the row position seems like too much. Is there a simpler way to do this?
I didn't exactly use Tim's answer, but it showed me the way:
SELECT D.GroupId, D.Item,
ROW_NUMBER() OVER (PARTITION BY D.GroupId ORDER BY D.Item) AS Pos,
COUNT(D.GroupId) OVER (PARTITION BY D.GroupId) AS GroupCount
FROM #Data D
You can use
COUNT(GroupID)OVER(PARTITION BY GroupID)
:OVER Clause (Transact-SQL)
Here's a fiddle: http://sqlfiddle.com/#!6/b6505/2/0
Note that it's not ordered since you haven't told how to order.