Hacer algo parecido a un identity pero seccionado

[sirguille]

Ayuda con el siguiente problema por favor.

Tengo la siguiente tabla:

CREATE TABLE #OFICINAS
(
COD_OFICINA NVARCHAR(5)
)

INSERT INTO #OFICINAS VALUES('OF001')
INSERT INTO #OFICINAS VALUES('OF001')
INSERT INTO #OFICINAS VALUES('OF001')
INSERT INTO #OFICINAS VALUES('OF002')
INSERT INTO #OFICINAS VALUES('OF002')
INSERT INTO #OFICINAS VALUES('OF002')
INSERT INTO #OFICINAS VALUES('OF002')
INSERT INTO #OFICINAS VALUES('OF003')
INSERT INTO #OFICINAS VALUES('OF003')
INSERT INTO #OFICINAS VALUES('OF003')
INSERT INTO #OFICINAS VALUES('OF003')


Mi duda es cómo crear una columna CORRELATIVO de tipo entero, que se enumere consecutivamente desde 1 hasta donde termine un COD_OFICINA, ejemplo:

COD_OFICINA | CORRELATIVO
OF001 | 1
OF001 | 2
OF001 | 3
OF002 | 1
OF002 | 2
OF002 | 3
OF002 | 4
OF003 | 1
OF003 | 2
OF003 | 3
OF003 | 4

Alguna idea para este problemilla?, gracias.

[iislas]

Te pongo un ejemplo con Ciudades:

create table ciudades(
PAIS VARCHAR(20) not null,
CIUDAD VARCHAR(20) not null
)
GO

insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'MONTERREY')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'GUADALAJARA')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'DISTRITO FEDERAL')
insert into ciudades(PAIS, CIUDAD)
values('MEXICO', 'TIJUANA')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'VALENCIA')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'MADRID')
insert into ciudades(PAIS, CIUDAD)
values('ESPAÑA', 'BARCELONA')
insert into ciudades(PAIS, CIUDAD)
values('ITALIA', 'ROMA')
insert into ciudades(PAIS, CIUDAD)
values('ITALIA', 'MILAN')
GO

SELECT PAIS, (SELECT COUNT(*)
FROM ciudades AS I
WHERE I.PAIS=J.PAIS
AND I.CIUDAD>=J.CIUDAD) AS No
, CIUDAD
FROM ciudades AS J
ORDER BY PAIS, No
go

DROP TABLE ciudades
GO

[Libras]

Prueba con esto:

select row_number() over (partition by cod_oficina order by cod_oficina) rn, cod_oficina from #oficinas

Saludos!