sql: 怎样查询数值最大的五条记录

[dubuxunmei]

表结构：
table_name:test
table_col: one numeric
two numeric

表数据：
one, two
31，21
10，21
56，23
54，32
99，54
87，64
47，15
70，31
请问怎样获取列one（从大到小）前3条记录，即
99，54
87，64
70，31
？？

[liuyi8903]

select * from (
select a.* ,row_number() over(order by one desc) rk from test a
)
where rk<=3;

[liuyi8903]

五条
select * from (
select a.* ,row_number() over(order by one desc) rk from test a
)
where rk<=5

[ORARichard]

create table tmp as select rowid no from test where rownum<6 order by desc;
select * from test,tmp where test.rowid=tmp.no;
drop table tmp;

[guanjm]

select top 3 one,two from test order by one desc
SQL2000里就这样就可以了

[guanjm]

select top 5 one,two from test order by one desc

[liuyi8903]

oracle里面是不支持top的.

[phant0m]

用 liuyi8903(西西) 的

[h2dog]

select * from
(select one from test order by one desc)
where rownum<=5

[liuyi8903]

数据量小的情况下都差不多.

[liuyi8903]

刚刚测试了一个大表.应该是select * from (select * from test order by one desc, two desc) where rownum<4 快些!

[jackywood]

select * from (select * from test order by one desc, two desc) where rownum<4;
也可以达到目的

有那位高手可以说明这句和liuyi8903(西西)的sql那一个的效率更好吗？为什么？
谢谢指教

[guangli_zhang]

select * from test
where rownum <=5
order by one desc

[bati_mfl]

select * from (
select a.* ,row_number() over(order by one desc) rk from test a
)
where rk<=5

[hoterran]

顶以下

[yeye0823]

select * from (select one from test order by one desc) where rownum<=5;

[overmind]

我觉得可以取出所有纪录，然后用程序判断。

[pbwl]

select * from test
where rownum <=5
order by one desc

[_baal_]

select * from test where rownum <=5 order by one desc这种是不行的，rownum<=5取出的是未排序前的头5条
select * from (select one from test order by one desc) where rownum<=5是正解