select no from biao order by no desc where rownum<2
啊……查最小的应该没有desc的……
order by 语句是sql语句里最后的处理的部分,所以说你的语句是先取记录再排序的,所以存在问题
SQL> SQL> with tablename as 2 ( 3 select 1 no from dual union all 4 select 2 from dual union all 5 select 4 from dual union all 6 select 5 from dual union all 7 select 6 from dual union all 8 select 7 from dual union all 9 select 9 from dual union all 10 select 10 from dual 11 ) 12 select min(no) 13 from (select rownum no 14 from dual 15 connect by rownum <= (select max(no) from tablename) 16 minus 17 select no from tablename) 18 ; MIN(NO) ---------- 3SQL>
忘了套一层了…… 应该是 select * from (select no from biao order by no ) where rownum<2
with tab as( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 8 from dual)select min(t.rm) from (select rownum rm from tab) t where t.rm not in (select * from tab)
http://topic.csdn.net/u/20110301/13/261af36d-9422-4a7d-8cd2-30da89f3a1a0.html?30600
SQL> with tablename as
2 (
3 select 1 no from dual union all
4 select 2 from dual union all
5 select 4 from dual union all
6 select 5 from dual union all
7 select 6 from dual union all
8 select 7 from dual union all
9 select 9 from dual union all
10 select 10 from dual
11 )
12 select min(no)
13 from (select rownum no
14 from dual
15 connect by rownum <= (select max(no) from tablename)
16 minus
17 select no from tablename)
18 ; MIN(NO)
----------
3SQL>
忘了套一层了……
应该是
select * from (select no from biao order by no ) where rownum<2
with tab as(
select 1 id from dual union all
select 2 from dual union all
select 4 from dual union all
select 5 from dual union all
select 7 from dual union all
select 8 from dual)select min(t.rm) from (select rownum rm from tab) t where t.rm not in (select * from tab)