怎样找到某条数据的上一条和下一条数据解决方案

怎样找到某条数据的上一条和下一条数据
我的数据库是这样的：

nInfoMain表
select * from nInfoMain order by TopFlag desc,IssueTime desc
ID text TopFlag time
280 内容 1 2011-04-07 11:03:05
164 内容 1 2011-03-07 11:03:05
583 内容 0 2011-11-05 11:03:05
251 内容 0 2011-06-11 11:03:05
283 内容 0 2011-04-05 11:03:05
211 内容 0 2011-03-24 11:03:05

实际就是顺序先按TopFlag=1(被置顶的内容)时间倒序排在前面，然后再按没有置顶的内容时间倒序排在后面

这样如果，已经知道本条数据是其中一条，如何查出他的上一条数据和下一条。

因为加入了TopFlag我就被搞昏了

自己写了个
select top 1 * from [nInfoMain] 
where time<(select time from [nInfoMain] 
where ID=251) and ID<>251 
不正确

[解决办法]

SQL code

with t as(select row_number() over(order by TopFlag desc,IssueTime desc) rn,* from nInfoMain)select * from t where rn in ([指定的rn]-1,[指定的rn]+1)
[解决办法]
SQL code
declare @rid intselect *,rid=identity(int,1,1)  into #tbfrom nInfoMain order by TopFlag desc,IssueTime descselect @rid = rid from #tb where --你查询哪条数据的条件select *from #tbwhere rid between @rid-1 and @rid+1
[解决办法]
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-11-24 11:36:15-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([ID] int,[text] varchar(4),[TopFlag] int,[time] datetime)insert [tb]select 280,'内容',1,'2011-04-07 11:03:05' union allselect 164,'内容',1,'2011-03-07 11:03:05' union allselect 583,'内容',0,'2011-11-05 11:03:05' union allselect 251,'内容',0,'2011-06-11 11:03:05' union allselect 283,'内容',0,'2011-04-05 11:03:05' union allselect 211,'内容',0,'2011-03-24 11:03:05'--------------开始查询--------------------------declare @id intset @id=164;with f as(select px=ROW_NUMBER()over(order by getdate()),* from tb)select ID,TEXT,topflag,time  from  f where px=(select px from f where ID=@id)-1union all select ID,TEXT,topflag,time  from  f where px=(select px from f where ID=@id)+1----------------结果----------------------------/* ID          TEXT topflag     time----------- ---- ----------- -----------------------280         内容   1           2011-04-07 11:03:05.000583         内容   0           2011-11-05 11:03:05.000(2 行受影响)*/