Get row numbers of records inside a group, also get the population of each group

Consider the following set of values:

PK	ID	State
1	1	TX
2	1	AZ
3	1	CA
4	1	NV
5	2	FL
6	2	AL
7	2	GA
8	3	NY
9	3	MA

Field PK is an auto-increment primary key, that uniquely identifies each record and also determines row order. Field ID groups together table records.

What we want to do here is to enumerate records inside each group, as well as to ouput the population of each group. This is the result we would like to see:

PK	ID	State	Rn	Cnt
1	1	TX	1	4
2	1	AZ	2	4
3	1	CA	3	4
4	1	NV	4	4
5	2	FL	1	3
6	2	AL	2	3
7	2	GA	3	3
8	3	NY	1	2
9	3	MA	2	2

In SQL Server this kind of result can be easily achieved using Window Functions. As their name implies, window functions operate on windows of data. We can determine how each window looks like using OVER clause. Using the following clause:

OVER (PARTITION BY ID ORDER BY PK)

produces these windows of data:

1. (1, 1, 'TX')-> (2, 1, 'AZ')-> (3, 1, 'CA')-> (4, 1, 'NV')
2. (5, 2, 'FL')-> (6, 2, 'AL')-> (7, 2, 'GA')
3. (8, 3, 'NY')-> (9, 3, 'MA')

We can easily get at the desired output using ROW_NUMBER to enumerate the records inside each partition and windowed version of COUNT aggregate function to get the population of each partition:

SELECT ID, State, 
       ROW_NUMBER OVER (PARTITION BY ID ORDER BY PK) AS rn,
       COUNT(*) OVER (PARTITION BY ID) AS Cnt
FROM mytable