SQL Server get next previous rows from a table based on a date

Question

I have the following SQL Server query and I am trying to get the next and previous row from a given Id. All works great unless there are two/more dates that are the same, then my logic breaks down.

If you set @currentId = 4 then I get back id 7 (it should be id 6) for the prev

If you set @currentId = 6 then I get back id 2 (it should be id 4) for the next

declare @t table (i int, d datetime)
insert into @t (i, d)
select 1, '17-Nov-2009 07:22:13' union
select 2, '18-Nov-2009 07:22:14' union
select 3, '17-Nov-2009 07:23:15' union
select 4, '20-Nov-2009 07:22:18' union
select 5, '17-Nov-2009 07:22:17' union
select 6, '20-Nov-2009 07:22:18' union
select 7, '21-Nov-2009 07:22:19'

--order that I want
select * from @t order by d desc, i

declare @currentId int; set @currentId = 4

--Get Prev
select TOP 1 * from @t where d > (select TOP 1 d from @t where i = @currentId order by d, i desc) order by d, i desc

--Get Next
select TOP 1 * from @t where d < (select TOP 1 d from @t where i = @currentId order by d desc) order by d desc

Can anyone help me work out how to guarantee get the next/prev row based on a given id, Note that it is important that I keep the this order, Date Desc, id ASC

Many thanks

EDIT: It should be noted that this is going to be used for SQL Server 2005.

Answer 1

Toy can try something like this

    declare @t table (i int, d datetime)
        insert into @t (i, d)
        select 1, '17-Nov-2009 07:22:13' union
        select 2, '18-Nov-2009 07:22:14' union
        select 3, '17-Nov-2009 07:23:15' union
        select 4, '20-Nov-2009 07:22:18' union
        select 5, '17-Nov-2009 07:22:17' union
        select 6, '20-Nov-2009 07:22:18' union
        select 7, '21-Nov-2009 07:22:19'

        --order that I want
        select * from @t order by d desc, i

        declare @currentId int; 
        set @currentId = 4



SELECT  TOP 1 t.*
FROM    @t t INNER JOIN
      (
       SELECT d CurrentDateVal
       FROM @t
       WHERE i = @currentId
      ) currentDate ON t.d <= currentDate.CurrentDateVal AND t.i != @currentId
    ORDER BY t.d DESC

    SELECT t.*
FROM    @t t INNER JOIN
      (
       SELECT d CurrentDateVal
       FROM @t
       WHERE i = @currentId
      ) currentDate ON t.d >= currentDate.CurrentDateVal AND t.i != @currentId
    ORDER BY t.d

You must be carefull, it can seem that 6 should be both prev and next.

Answer 2

You were nearly there ... try this instead. Effectively changed the date comparison to be "...-or-equals-to" and telling it not to match the current ID so it doesn't return the same row...

declare @currID int
set @currID = 4

select top 1 *
from (
    select * 
        from @t
        where d = (select d from @t where i = @currID)
            and i > @currID
    union ALL
    select * 
        from @t
        where d < (select d from @t where i = @currID)
) as t
order by d desc, i

select top 1 *
from (
    select * 
        from @t
        where d = (select d from @t where i = @currID)
            and i < @currID
    union ALL
    select * 
        from @t
        where d > (select d from @t where i = @currID)
) as t
order by d, i desc