Maxima Cuota y la anterior

[damimg]

Iniciado por Libras

Código SQL:

CREATE 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 * 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-1)=t2.rn)
WHERE t2.id IS NOT NULL AND CONVERT(VARCHAR(20),t1.rn) + '|' + CONVERT(VARCHAR(20),t1.id) IN (SELECT CONVERT(VARCHAR(20),MAX(rn))  + '|' + CONVERT(VARCHAR(20),id) FROM(
SELECT ROW_NUMBER() OVER(partition BY id ORDER BY fecha) AS rn,* FROM #temp) AS t1 GROUP BY id)
 

Gracias Libras, se podría aplicar lo mismo si el motor fuese Oracle?