sql server - SQL - Return value from row above -

i have table like:

book        ¦time out       ¦time in  123456789   ¦01/01/2013 ¦07/07/2013 123456788   ¦15/01/2013 ¦20/01/2013 123456788   ¦23/01/2013 ¦30/01/2013 123144563   ¦01/02/2013 ¦18/02/2013 123144563   ¦20/02/2013 ¦null 124567892   ¦03/03/2013 ¦10/03/2013 

i this:

book        ¦time out       ¦time in        ¦next time out 123456789   ¦01/01/2013     ¦07/07/2013     ¦null 123456788   ¦15/01/2013     ¦20/01/2013     ¦23/01/2013 123456788   ¦23/01/2013     ¦30/01/2013     ¦null 123144563   ¦01/02/2013     ¦18/02/2013     ¦20/02/2013 123144563   ¦20/02/2013     ¦null           ¦null 124567892   ¦03/03/2013     ¦10/03/2013     ¦null 

code:

select nextout.book,        nextout.[time in] nexttimein   booktable nextout        join booktable nextoutsec          on nextout.book = nextoutsec.book  nextout.[time in] = (select max(maxtbl.[time in])                               booktable maxtbl                              maxtbl.book = nextout.book)  

this returns duplicate book id's same 'next time out'. rather 1 correct value , 1 null value.

thank you!

untested following should started

;with q (   select book, [time in], row_number() on (partition book order [time in]) rn     booktable ) select  bt.*, q2.[time in] nexttimein    booktable bt         inner join q q1 on q1.book = bt.book , isnull(q1.[time in], 0) = isnull(bt.[time in], 0)         left outer join q q2 on q2.book = q1.book , q2.rn = q1.rn + 1 

the gist of is

• q adds row number each book, ordered [time in]
• q2 joins q1 next [time in] value.
• q1 joins booktable original values