select version from versioninfo where versiontype = '02' and rownum=1 order by releasedate desc
先排序,最后 加上where rownum<N
这个问题问的太多了,你可以查一下ROWNUM,详细用法论坛里面多的很啊
select * from table where rownum<n+1 order by 条件
select version from versioninfo where rownum <2 and versiontype = '02' order by releasedate desc
select * from (select version from versioninfo where versiontype = '02' and rownum=1 order by releasedate desc) a order by version
使用ROWNUM可以解決的select version from ( select a.*,rownum rn from versioninfo a where versiontype = '02' order by releasedate desc ) where rn <= 1
select * from (select version from versioninfo where versiontype = '02' order by releasedate desc) a where and rownum=1 order by version
这里描述的 SELECT N 包括这样几种情况: 1. 选取TOP N行记录 2. 选取N1-N2行记录 3. 选取FOOT N行记录 当然需要考虑是否有ORDER BY子句的情况, 下面试以系统视图CAT为例分别说明. 注: A. 为没有ORDER BY的情况 B. 有ORDER BY的情况1. 选取 TOP N 行记录 A. SELECT * FROM CAT WHERE ROWNUM<=N B. SELECT * FROM ( SELECT * FROM CAT ORDER BY TABLE_TYPE ) WHERE ROWNUM<=N2. 选取N1-N2行记录 A. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ) WHERE ROWSEQ BETWEEN N1 AND N2; 或: SELECT * FROM CAT WHERE ROWNUM<=N2 MINUS SELECT * FROM CAT WHERE ROWNUM<N1 B. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X) WHERE ROWSEQ BETWEEN N1+1 AND N2;3. 选取FOOT N行记录 这里是说明不知道记录集的记录个数的情况, 如果已知, 用上面2的方法即可 A. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ) WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT ) B. SELECT TABLE_NAME,TABLE_TYPE FROM ( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE) WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT ) 或 SELECT * FROM ( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC) WHERE ROWNUM<=N
to ORARichard(没钱的日子好难过啊) ,baojianjun(包子) ,mqmmx(奔腾2000):要是我没有记错的话...在oracle当中....subquery是不可以有order by的 在一个select statement中只能有一个order by,而且必须是在statement的最后
饿...刚才试了一下,放在from里面是可以的,看来是我记错了
像这样 是不行的: “select version from versioninfo where rownum <2 and versiontype = '02' order by releasedate desc” 总之要先排好序(在子查询中),多然后再在主查询中使用ROWNUM。============= 请问有没有大哥就如下问题给出答案: 可不可以把GROUP BY 与ROWNUM 一起使用,以达到对每个分组(每个分组已order by 了)的前几条记录进行某种操作(如读取记录)的目的。 例如,从求学经历表中,选出每个人的最高学历,一个人可能有几个最高学历(如双学士学位),要用GROUP BY 哦,不用不算!
加上where rownum<N
where rownum <2 and
versiontype = '02'
order by releasedate desc
from (
select a.*,rownum rn
from versioninfo a
where versiontype = '02'
order by releasedate desc
)
where rn <= 1
1. 选取TOP N行记录
2. 选取N1-N2行记录
3. 选取FOOT N行记录
当然需要考虑是否有ORDER BY子句的情况, 下面试以系统视图CAT为例分别说明.
注: A. 为没有ORDER BY的情况
B. 有ORDER BY的情况1. 选取 TOP N 行记录
A. SELECT * FROM CAT WHERE ROWNUM<=N
B. SELECT * FROM
( SELECT * FROM CAT ORDER BY TABLE_TYPE )
WHERE ROWNUM<=N2. 选取N1-N2行记录
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ BETWEEN N1 AND N2;
或:
SELECT * FROM CAT WHERE ROWNUM<=N2
MINUS
SELECT * FROM CAT WHERE ROWNUM<N1
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)
WHERE ROWSEQ BETWEEN N1+1 AND N2;3. 选取FOOT N行记录
这里是说明不知道记录集的记录个数的情况, 如果已知, 用上面2的方法即可
A. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
B. SELECT TABLE_NAME,TABLE_TYPE FROM
( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)
WHERE ROWSEQ > ( SELECT COUNT(*)-N FROM CAT )
或
SELECT * FROM
( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)
WHERE ROWNUM<=N
在一个select statement中只能有一个order by,而且必须是在statement的最后
“select version from versioninfo where rownum <2 and versiontype = '02'
order by releasedate desc”
总之要先排好序(在子查询中),多然后再在主查询中使用ROWNUM。=============
请问有没有大哥就如下问题给出答案:
可不可以把GROUP BY 与ROWNUM 一起使用,以达到对每个分组(每个分组已order by 了)的前几条记录进行某种操作(如读取记录)的目的。
例如,从求学经历表中,选出每个人的最高学历,一个人可能有几个最高学历(如双学士学位),要用GROUP BY 哦,不用不算!