This will give you the same result, but you would really need a better column to order by in the row_number(possibly dt to keep them in chronological order). -- create a test table and populateCREATE TABLE #tmp(id VARCHAR(5), Dt DATETIME);INSERT INTO #tmp VALUES ('sdfew','20120101');INSERT INTO #tmp VALUES ('sdfew','20120107');INSERT INTO #tmp VALUES ('sdfew','20120112');INSERT INTO #tmp VALUES ('sdfew','20120122');-- select id, date and date from the next row.with cte (id,dt,rn)as(SELECT id ,Dt AS DateFromThisRow ,ROW_NUMBER() OVER(order by id) as RNFROM #tmp )SELECT a.id, a.Dt AS DateFromThisRow, b.Dt AS DateFromNextRowFROM cte aLEFT JOIN cte b ON a.rn+1 = b.rn;drop table #tmp
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx