select top 1 from (select top 900 from tb order by field) A order by field desc
select top 1 * from T where 主健 not in(select top 500 主健 from T)--查501行
select top 1 a.* from (select top 3* from test order by analystid) a order by a.analystid desc
create table #tmp ( myid int, mytext varchar(10) )insert into #tmp select 1,'1 asdf' union all select 2,'2 asdf' union all select 3,'3 asdf' union all select 4,'4 asdf' union all select 5,'5 asdf' union all select 6,'6 asdf' union all select 7,'7 asdf'select * from #tmp order by myid--以下查询第7条 select top 1 * from #tmp where myid not in (select top 6 myid from #tmp)--以下查询第5条 --该方法不太严谨,因为就算不存在,也会返回一条记录 select top 1 * from (select top 5 * from #tmp order by myid) a order by a.myid desc
因定义上记录并无先后,必须定出按哪些字段排序,第900行的信息才能唯一确定 select top 1 from (select top 900 * from 表 order by 排序列 升序) as a order by 排序列 降序
--没有主键,没有排序,通过游标定位,测试:select top 1000 id=identity(int,1,1) into #T from syscolumns,sysobjectsdeclare cur_test cursor SCROLL for select top 900 * from #T open cur_test fetch absolute 900 from cur_test close cur_test deallocate cur_test/* id ---------- 900 */drop table #T
create table #tmp
(
myid int,
mytext varchar(10)
)insert into #tmp
select 1,'1 asdf' union all
select 2,'2 asdf' union all
select 3,'3 asdf' union all
select 4,'4 asdf' union all
select 5,'5 asdf' union all
select 6,'6 asdf' union all
select 7,'7 asdf'select * from #tmp order by myid--以下查询第7条
select top 1 * from #tmp
where myid not in (select top 6 myid from #tmp)--以下查询第5条
--该方法不太严谨,因为就算不存在,也会返回一条记录
select top 1 * from (select top 5 * from #tmp order by myid) a order by a.myid desc
select top 1 from (select top 900 * from 表 order by 排序列 升序) as a
order by 排序列 降序
open cur_test
fetch absolute 900 from cur_test
close cur_test
deallocate cur_test/*
id
----------
900
*/drop table #T