Código SQL:
Ver originalCREATE TABLE #temp
(
id INT,
fecha datetime
)
INSERT INTO #temp VALUES (900681,'01/04/2013')
INSERT INTO #temp VALUES (900681,'01/05/2013')
INSERT INTO #temp VALUES (900681,'01/06/2013')
INSERT INTO #temp VALUES (900525,'04/03/2010')
INSERT INTO #temp VALUES (900525,'01/02/2010')
INSERT INTO #temp VALUES (900526,'01/03/2010')
INSERT INTO #temp VALUES (900526,'01/04/2010')
INSERT INTO #temp VALUES (900526,'01/05/2010')
INSERT INTO #temp VALUES (900526,'01/06/2010')
SELECT t1.rn, CASE WHEN t1.id=t2.id THEN 0 ELSE t1.id END AS test, t1.fecha FROM(
SELECT ROW_NUMBER() OVER(partition BY id ORDER BY fecha) AS rn,* FROM #temp
) AS t1 LEFT JOIN
(SELECT ROW_NUMBER() OVER(partition BY id ORDER BY fecha) AS rn,* FROM #temp) AS t2 ON (t1.id=t2.id AND (t1.rn)=t2.rn+1)
revisa este query, regresa un resultado parecido al que buscas :)
rn test fecha
1 900525 2010-01-02 00:00:00.000
2 0 2010-04-03 00:00:00.000
1 900526 2010-01-03 00:00:00.000
2 0 2010-01-04 00:00:00.000
3 0 2010-01-05 00:00:00.000
4 0 2010-01-06 00:00:00.000
1 900681 2013-01-04 00:00:00.000
2 0 2013-01-05 00:00:00.000
3 0 2013-01-06 00:00:00.000
Si te fijas el campo id esta repetido, y lo que hace el query es poner en los repetidos el valor de 0 y solo dejar 1(el de la fecha menor)