这样的查询语句如何最好？比较特殊

这样的查询语句怎么最好？比较特殊
表Test结构为两个字段，id与timedate，id为自动增长，timedate为时间，id唯一，时间，有可能会相同，也有可能为空。
现已知id值，我要通过id查询一条记录，查询规则为按时间降序，id降序的下一条记录，
select * from Test order by timedate desc,id desc 即按条语句查询结果，找出该id所在记录的下一条记录。
测试数据如下：
id timedate
1 2011-7-11 0:00:00
2 2011-6-11 0:00:00
3 2011-6-11 0:00:00
4 2011-6-11 0:00:00
5 2011-9-11 0:00:00
6 2011-5-11 0:00:00

通过select * from Test order by timedate desc,id desc 排序后：
id timedate
5 2011-9-11 0:00:00
1 2011-7-11 0:00:00
4 2011-6-11 0:00:00
3 2011-6-11 0:00:00
2 2011-6-11 0:00:00
6 2011-5-11 0:00:00
现在我想实现的是这样的：
如果id=4,那么，我想查到id=3这条记录，
如果id=3,那么，我想查到id=2这条记录，
如果id=2,那么，我想查到id=6这条记录

该怎么写呢？下面这样写不行：select * from Test where id = 4 and timedate <= (select timedate from Test where id=4)

这个看起来简单，没想到，试了半天没试出来，请各位大虾帮忙了，先谢了。

[解决办法]

SQL code

------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-12-26 14:53:26-- 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,[timedate] datetime)insert [tb]select 1,'2011-7-11 0:00:00' union allselect 2,'2011-6-11 0:00:00' union allselect 3,'2011-6-11 0:00:00' union allselect 4,'2011-6-11 0:00:00' union allselect 5,'2011-9-11 0:00:00' union allselect 6,'2011-5-11 0:00:00'--------------开始查询--------------------------;with f as(select px=ROW_NUMBER()over(order by timedate desc,id desc),* from tb)select id,timedate from f where px=(select px+1 from f where ID=3)----------------结果----------------------------/*id          timedate----------- -----------------------2           2011-06-11 00:00:00.000(1 行受影响)*/
[解决办法]
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx

mysql参考上面的这个
[解决办法]
try this,
SQL code
declare @x intselect @x:=ifnull(@x,0)+1 as rownum,id,timedateinto #tfrom taborder by timedate desc,id descselect * from #t where rownum=(select rownum+1 from #t where id=[指定的id])drop table #t
[解决办法]
SQL code
create table tb(id int,timedate datetime)insert into tb select 1,'2011-7-11 0:00:00'insert into tb select 2,'2011-6-11 0:00:00'insert into tb select 3,'2011-6-11 0:00:00'insert into tb select 4,'2011-6-11 0:00:00'insert into tb select 5,'2011-9-11 0:00:00'insert into tb select 6,'2011-5-11 0:00:00'godeclare @id intset @id=2select top 1 * from tb awhere timedate<(select timedate from tb where id=@id)or id<(select top 1 id from tb where timedate=a.timedate and id<=@id order by id desc)order by timedate desc,id desc/*id          timedate----------- -----------------------6           2011-05-11 00:00:00.000(1 行受影响)*/godrop table tb
[解决办法]
MSSQL2005及以上：
SQL code
 create table tb(id int,timedate datetime)insert into tb select 1,'2011-7-11 0:00:00';insert into tb select 2,'2011-6-11 0:00:00';insert into tb select 3,'2011-6-11 0:00:00';insert into tb select 4,'2011-6-11 0:00:00';insert into tb select 5,'2011-9-11 0:00:00';insert into tb select 6,'2011-5-11 0:00:00';go;WITH cte_1 AS (SELECT ROW_NUMBER() OVER (ORDER BY timedate DESC,id DESC) AS rn,idFROM dbo.tb)SELECT * FROM cte_1 AS A WHERE rn=(SELECT rn+1 FROM cte_1 AS B WHERE id=3)